Add Local Administrators as SQL Server Administrator using PowerShell

Ever faced a situation when you are stuck with a SQL Server when you don’t have access even though if you are the Local Administrator.

Luckily, there is a way to get yourself access in this situation and you can use the following script.

It uses the SQL Server Single-User Mode to start the SQL Server. Starting SQL Server in single-user mode enables any member of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. For more information, see Connect to SQL Server When System Administrators Are Locked Out.

Download Script

$ServiceName = "MSSQLSERVER" #Enter the service name for your SQL Server Instance (MSSQLSERVER by default)
$Server = "Manas" #Enter the name of SQL Server Instance

NET STOP $ServiceName 
NET START $ServiceName /mSQLCMD 

SQLCMD -S $Server -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 

NET STOP $ServiceName 
NET START $ServiceName

SQLCMD -S $Server -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"