It seems that a lot of my recent posts are related to my “adventures” at work. Oh well… <shrugs> Here’s another!

We have a legacy SQL server (Windows 2012R2, SQL 2012) that was unmaintained for a number of years until I came along. We had a developer who was nominally responsible for it, but I discovered that even his credentials didn’t have full sa rights, and some databases were simply not accessible by him. I decided to turn to a life of crime and break in.

Is it really breaking in when I follow MS documentation? I suppose not.

You can start an instance of SQL Server in single-user mode with either the -m or -f options from the command line. Any member of the computer’s local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role. When you start the instance in single-user mode, first stop the SQL Server Agent service. Otherwise, SQL Server Agent might connect first, taking the only available connection to the server and blocking you from logging in.

1. Open a Windows PowerShell command. Run it as an Administrator
2. Define the variables to use later:

   $service_name = "MSSQL`$instancename"  
   $sql_server_instance = "machine_name\instance"  
   $login_to_be_granted_access = "[CONTOSO\PatK]"
   

3. Stop SQL Server service:

   `net stop $SERVICENAME`
   

4. Execute a CREATE LOGIN command followed by ALTER SERVER ROLE command"

   `sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN $login_to_be_granted_access FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "`
   

5. Exit, and restart the service normally

References

Connect to SQL Server when system administrators are locked out