Saturday, May 12, 2012

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.

1 comment:

  1. You just made my day. After accidentally deleting the database owner account, and realizing that the sa account was disabled, I thought I was going to have a very long and painful day ahead of me. Thanks for saving me!