Recover sa account

Today, a friend from the office locked himself out of SqlServer 2008 R2 Express, somehow he managed to remove the windows Administrator account form the list of sysadmin accounts in the SqlServer express installed in a development server.

I thought there was no way to recover from a mistake a like that, but another friend found the way and gave us a link with the solution.

Basically, we need to stop the SqlService, and restart it in "single user mode" and "minimal config mode"

sqlservr.exe -m -r -s SQLEXPRESS

And the open another command window and run this commands:

osql -E -S .\SQLEXPRESS 
exec sp_password @new='changeme', @loginame='sa' 
go 
alter login sa enable 
go 
exit 

And that was it. Remember, without the special "-m -r" options, the osql commands will fail.

NOTE: In case your SQL Server is not configured to use Mixed Mode for authentication, you may also need to make some modification to the registry settings.

Basically you need to find the registry entry for the instance:

Default instance:

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

And  change the value of LoginMode to 2.