How to recover SA password on Microsoft SQL Server 2008 R2

from http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

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.
  Stop the SQL service again
  • sp_addsrvrolemember ‘recovery’, ‘sysadmin’
  • go
• 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.