By Hally | Last Updated
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.
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.
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.
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.
Preparation:
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:
