How do I give myself admin rights to a sql server instance, having forgotten the sa password?

4

4

I have lost all passwords for a SQL Server instance, how do I give myself admin access again?

Ben

Posted 2010-01-09T00:07:41.470

Reputation: 3 798

Answers

1

Run SSMS as Administrator (ie, with escalated privileges on the server). This should have sysadmin rights, and you can then find your login and assign rights as necessary.

Almost wrote "tights" there. You can probably assign them too, if you're running as an admin on the server.

Rob Farley

Posted 2010-01-09T00:07:41.470

Reputation: 343

8

  1. Open Configuration Manager

  2. Stop the server instance

  3. Right, click >> properties >> advanced >> startup parameters

  4. Add "-m;" right at the start of the string ensuring there are no spaces anywhere in the string

  5. Restart server

  6. sqlcmd -S localhost from the command prompt

  7. EXEC sp_addsrvrolemember 'domain\username', 'sysadmin'; GO

  8. exit

  9. Ensure can connect from command prompt sqlcmd -S localhost -E (if logged-in as that user to windows)

  10. Remove "-m;" from startup parameters

  11. Restart the database server

Ben

Posted 2010-01-09T00:07:41.470

Reputation: 3 798

2

And with screenshots! http://blogs.ameriteach.com/chris-randall/2009/12/11/sql-server-2008-forgot-to-add-an-administrator-account.html

– ta.speot.is – 2010-03-22T02:25:12.857

For some reason this didn't work for me: step 9 was unable to connect. But note that at step 6 you can instead run SSMS locally and create the login that way. That worked for me. – RomanSt – 2015-11-20T07:44:59.113

Asked: 2010-01-09T00:07:41.470

Viewed: 18 261 times

Active: 2010-03-22T01:57:18.347