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.
$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.'"