It's a long time since the 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.
- Method 1: Through SQL Server Management Studio
- Method 2: Using SQL Password Refixer program
Method 1: Reset SQL Server 2008 R2 forgotten SA password through Management Studio
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.
Method 2: Reset SQL Server 2008 R2 SA password using SQL Password Refixer
When SQL Server 2008 R2 forgot SA password and Windows Authentication don't work either, 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.
Related Articles
Lost SA password SQL Server 2008 R2? How can we retrieve or reset it effectively without damage on SQL Server database?
Now in this passage, two ways are introduced in two situations for SQL Server users to choose for SQL Server 2008 R2 SA password reset. Select one appropriate for your SQL Server 2008 R2.
- Way 1: Retrieve SQL Server 2008 R2 SA Lost Password with Windows Local Admin User
- Way 2: Retrieve SQL Server 2008 R2 Lost SA Password with Password Recovery tool
Way 1: Retrieve SQL Server 2008 R2 SA Lost Password with Windows Local Admin User
After SA password lost, if there is any other Windows login that is a member of Windows admin group, please login in Windows system with that account. Then start SQL Server in Single User Mode. Once it is done, you will be only able to connect SQL Server using sqlcmd utility with Windows Authentication. However, this way is disaster recovery, so only being used when no other recovery method available. It allows Windows administrator override within SQL Server.
Steps to retrieve SA lost password with Windows local admin user
Step 1: Stop SQL Server and restart it in single user mode.
Tips: How to Set SQL Server Access with Single User Mode
Step 2: Connect to SQL Server using Windows credentials.
Step 3: Retrieve SA lost password in SQL Server 2008 R2.
3-1 Navigate to the SQL Server Logins, select SA and right-click on it to choose Properties.
3-2 In pop-up Login Properties window, type new password in Password and Confirm password box.
3-3 Press OK and exit Microsoft SQL Server 2008 R2 Management Studio.
You could refer to first method in how to recover SQL Server 2014 SA password.
Way 2: Retrieve SQL Server 2008 R2 Lost SA Password with Password Recovery Tool
If there is an effective SQL password recovery tool available, way 1 won't be recommended, because you could easily reset SQL Server SA password and other user passwords with SQL password recovery tool, such as SQL Password Genius. And there would be no effect on SQL Server database except SA password.
Steps to retrieve SQL 2008 R2 lost SA password with SQL Password Genius
Preparation:
1. Stop SQL Server services.
2. Download and install iSunshare SQL Password Genius on computer.
Step 1: Run SQL Password Genius and add master.mdf file.
Click Open File button on SQL Password Genius and navigate to the location C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Usually master.mdf file is located there. Select it to add into SQL password recovery software.
Step 2: Select SA account to reset password.
2-1 In user list, choose SA account and click on Reset button below list.
2-2 Enter new password in password box for SA account and click OK.
2-3 While you're prompted SA password has been successfully changed, click OK. And you could see new password has appeared in user list.
Related Articles:
- 4 Ways to Change SQL Server SA Password
- Reset Disabled SA Password without Admin Rights
- How to Reset User Password on Windows Server 2008 R2
- Reset Windows Server 2012 Domain Password