It’s a long time since last time I used SQL Server 2008 r2. Today I opened it but found I forgot SA password and failed to connect to database. I have no choice but reset SA password. Following I share 2 methods to reset SA password in SQL Server 2008 r2.
Step 1: Launch SQL Server Management Studio. Select Windows Authentication to connect to server.
Step 2: Expand the Logins folder under the Security folder. Then right click sa account and select Properties.
Step 3: On the login properties dialog box, you can remove the forgotten SA password and type a new password. Then click OK to save changes so that you can change SA password to a new one.
When SQL Server 2008 r2 forgot SA password and Windows Authentication don’t work neither, then use the specialized program – SQL Password Refixer to reset SA password.
Step 1: Install SQL Password Refixer on your computer with SQL Server 2008 r2 installed.
Step 2: Stop SQL Server 2008 r2 service.
Open SQL Server Configuration Manager to stop SQL Server instance service.
Step 3: Access to master.mdf file.
All users including the SA account passwords are stored in SQL Server master.mdf file. And "SQL Password Refixer" can access to the master.mdf file and help you reset the password.
Launch SQL Password Refixer. Click Open File button. On the Open dialog navigate to the path to the SQL Server master.mdf file. Select the file and click Open button to import the file into "SQL Password Refixer" program.
Step 4: Reset SA password.
Once master.mdf file is imported into this program. It accesses to the file and shows you all users including the SA account on SQL Server 2008 r2. Select to highlight the sa account and then click Reset button.
In the Reset Password dialog type a new password for the sa account. Click OK to confirm.
SA password is successfully reset to a new password and showed in the list.
Step 5: Restart SQL Server service.
Restart SQL Server service and then use the newly reset SA password to login to SQL Server 2008 r2 in SQL Server Authentication mode.