How to recover SA password on Microsoft SQL Server 2008 R2 | Forgot Password For Sql Server Recover

Описание

When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.

Step 1: Configure SQL Single-User Mode
1. Open SQL Server Configuration Manager
2. Stop the SQL Server Instance you need to recover the SA password
3. Open the properties on the SQL Server Instance and click on the Advanced tab
4. Change the Startup parameter by adding -m; at the begging of the line and click OK
5. Start the SQL Service Instance

Step 2: Use SQLCMD to add a Sysadmin Account
1. Open the command prompt
2. Run sqlcmd and press enter
3. Run a Transact-SQL (Below) command to add an existing account or a new account to the sysadmin server role. Replace DOMAIN\Username with the account you want to add. *NOTE: if you receive errors, I’ve listed some common issues at the end of this article.
EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin'; GO

Step 3: Un-configer SQL Single-User Mode
1. Open SQL Server Configuration Manager
2. Stop the SQL Server Instance
3. Open the properties on the SQL Server Instance and click on the Advanced tab
4. Change the Startup parameter by removing the -m; at the begging of the line and click OK
5. Start the SQL Service Instance

Step 4: Reset sa Password
1. Open Microsoft SQL Server Management Studio and login with the account you added
2. Under the DB, expand Security, then Logins
3. Open the properties for the sa account, and reset the password