Re-enabling sa account - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Re-enabling sa account

Author

Topic

CI
Starting Member

1 Post

Posted - 2009-08-14 : 17:00:43

My question is: Is there some way to re-enable "sa" built-in system account in SQL Server 2008, without using SSMS (using Registry or PowerShell)????

I am asking that question because, I've deleted my administrator (Windows Domain) Login, and disabled sa SQL Server Login, so now I can't access that particular instance of SQL Server. To re-gain access to that instance of SQL Server I must re-enable "sa" SQL Login, so that I could login as "sa" using SQL Server Authentification mode?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-18 : 06:21:02

Try this: http://technet.erpsalon.org/2009/04/recover-sysadmin-account-for-sql.html

{quote}
start sql in single user mode by appending ';-m' to start-up paramaters throught configuration manager;
stop all other sql related service other than db engine;
run sqlcmd -E
run 'create login [contoso\administrator] from windows;
run 'EXEC sp_addsrvrolemember 'CONTOSO\administrator', 'sysadmin';
remove ;-m
restart sql db engine
done!!!
{/quote}

I've done this by starting SQL from the command line, making my login sa, and restarting SQL as normal.

wpher56
Starting Member

1 Post

Posted - 2009-09-24 : 08:26:51

Many thanks for this trick! I had deleted a group that had sysadmin rights, and the sa account was not active. I could create an administrator user with sysadmin rights and reconnect to my instance.
Pierrot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources