Regain control of SQL Server

I started using a VM from a template someone else built in the last couple of days. My account was a local machine admin, but not a SQL Server admin. I actually had no access configured in SQL Server for my account. But I needed to get control over it and had no one available to help me in that moment.

In order to get sysadmin permissions on SQL Server, I did this:

1. Shut down SQL Server Services
2. Open a command line as Administrator
3. Change to you SQL Server Binn dir

• cd "c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn"

4. Run SQL Server in single-user mode

• sqlservr.exe -m -s MSSQLSERVER

5. Open a new command line as Administrator
6. Connect to SQL Server

• sqlcmd -S .\ (or sqlcmd -S .\MSSQLSERVER)

7.  Execute commands

• 1> sp_addsrvrolemember 'domain\user', 'sysadmin'
• 2> GO

8. CTRL+C in both windows to exit sessions
9. Restart SQL Server Services 
 

PS: You may need to be quick connecting to SQL (step 6) after starting it in single-user mode (step 4), otherwise some other process may pick up the session (remember, obviously, only one connection will be allowed...we're running in single-user mode!). Not a bad idea to have both command line windows open and ready to click Enter.