This is a very cool and useful tip if you find yourself locked out of the sysadmin role in sql server.

I have a SQL Server instance with the sa account disabled and all other logins removed to simulate a situation were I am locked out of SQL Server. My domain/windows account is a local administrator on my machine.

1. Stop the SQL Server engine service using configuration manager.
2. Open a command prompt and navigate to the binn folder of your SQL Server instance.
3. Start SQL Server in single user mode: for a default instance:

   sqlservr -m

   For a named instance:

   sqlservr -m -s 'instance name'

4. Open another command window and connect to the instance with sqlcmd. For a default instance:

   sqlcmd -E

   For a named instance:

   sqlcmd -E -S 'machine name\instance name'

   The -E tells sqlcmd to establish a trusted connection, and this is why we should be logged in as a local administrator at this point.

5. Create a SQL Login:

   CREATE LOGIN rescue WITH PASSWORD = 'P@$$w0rd1';
   GO

6. Add the login to the sysadmin group:

   EXEC sp_addsrvrolemember [rescue],[sysadmin];
   GO

   

7. Go back to the first command window and stop the single user mode SQL instance by pressing <ctrl>+<break>
8. Start the SQL Server service as normal using configuration manager, and log in with your newly created sys admin account.
   
9. Now you can re add in any deleted logins, and assign sysadmin role membership as needed.