iSumsoft» SQL Server » How to Reset Forgotten SA Password in SQL Server 2008 R2

How to Reset Forgotten SA Password in SQL Server 2008 R2

Ralph Adolphs
Ralph Adolphs

Updated:

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

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.

right-click sa 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.

Remove forgotten sa password and reset 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.

open master.mdf file

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.

Select sa account and click Reset button

In the Reset Password dialog, type a new password for the sa account. Click OK to confirm.

Type a new password for sa account

SA password is successfully reset to a new password and showed in the list.

SA password is successfully reset

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.