When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.
There can be different reasons you lost the password
- Person who installed the SQL Server knows the password but has left the building.
- You did not write down the password in your password file
- Password file is lost
- …
Steps to recover the SA password
-
Start SQL Server Configuration Manager
Stop the SQL services
Edit the properties of the SQL Service
Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters
- Start the SQL services. These are now running in Single User Mode.
- Start CMD on tthe SQL server
-
Start the SQLCMD command. Now you will see following screen
-
Now we create a new user. Enter following commands
- CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′; (Remember SQL server has default strong password policy
-
Go
- Now this user is created
-
Now we grant the user a SYSADMIN roles using the same SQLCMD window.
- EXEC sp_addsrvrolemember ‘recovery’, ‘sysadmin’;
- go
- Stop the SQL service again
-
Change the SQL service properties back to the default settings
- Start the SQL service again and use the new created login (recovery in my example)
-
Go via the security panel to the properties and change the password of the SA account.
- Now write down the new SA password.
