How to recover SA password on SQL Server 2008 R2

1. Open the Configuration Manager tool from the "SQL Server 2005| Configuration" menu

2. Stop the SQL Server Instance you need to recover

3. Navigate to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option

4. Click the “OK” button and restart the SQL Server Instance

NOTE: make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

5. After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility using Windows authentication. You can use Transact-SQL commands such as "sp_addsrvrolemember" to add an existing login (or a newly created one) to the sysadmin server role.

The following example adds the account "Buck" in the "CONTOSO" domain to the SQL Server "sysadmin" role:

EXEC sp_addsrvrolemember 'CONTOSO\Buck', 'sysadmin';

GO

6. Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance

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.
  • 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.