MS SQL Server 2008 and getting access

Couldn’t get into a 2008 SQL server today, whoops!

No one had the credentials so I had to get myself access. I found an article covering adding yourself via single user mode, but thought I’d add some more details for my own information and to cover missing info on that page.

  1. Shut down any services related to SQL and anything that connects to it. The ones I shut down on the server side were:
    1. SQL Server VSS Writer
    2. SQL Server (MSSQLSERVER)
  2. Next step, start SQL in sngle user mode

    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe -m
    

  3. Eventually it’ll show that SQL server is running

  4. Start up another command prompt in Administrator mode, then run the following commands. Replace SERVERNAME with the local hostname, and username with your admin account username.

    sqlcmd -E
    CREATE LOGIN [SERVERNAME\username] FROM WINDOWS
    go
    exec sp_addsrvrolemember @loginname=‘SERVERNAME\username’, @rolename=‘sysadmin’
    go

  5. Type “exit” and hit enter to close out of that SQL command line agent, then Ctrl-C in the SQL server window to shut it down.

  6. Start the server back up using services.msc.



#HOWTO #Microsoft SQL Server #Work