Promotion Online Store Other Languages

What to Do If you Forgot SA Password in SQL Server?

Elva Chrys

Apr 09,2012 06:23 / Posted by to MS SQL Server Tips
Follow @Elvabluesky

By default, you can login to SQL Server with windows admin account. If you followed the security instructions of removing the windows built-in\Administrators from the sysadmin server role when you forgot SA password, what to do? Reinstall SQL Server? No, it is not needed.
Here are two solutions for you to login to SQL server again which won't damage the data in it.

Solution 1: Employ SQL Server Password Unlocker

MS SQL Password Unlocker guarantees nearly 100% success rate to change forgotten SA and other login passwords into a new one through the master .mdf file, no matter how long and how complicated the old password is. The tool supports MS SQL Server 2008/2005/2000. Steps are following and take ways to reset the forgot SQL Ppassword:

Step 1:Download MS SQL Server Password Unlocker on your local PC, install and launch it.

Step 2: Click the Open button to import the file of master.mdf. And then, all the login names of your MS SQL Server will be displayed.

Step 3: Select the SA account and then click Change Password button.

Step 4: Enter your new password and click OK. Your SA password will be reset.

This is the simplest way to login to SQL Server again when you forgot SA password, and cannot access the server by windows authentication.

Solution 2: Make use of single-user mode

Members in Windows Administrators group could access to SQL Server when SQL Server is started in single-user mode, also known as maintenance mode. Thus, you can use it to reset the forgotten SA password.

In order to start SQL Server in single-user mode, use the following steps:

Step 1: Open the Configuration Manager tool from the SQL Server 2005| Configuration menu;

Step 2: Stop the SQL Server Instance you need to recover;

Step 3: Navigate to the Advanced tab, and in the Properties text box add ";–m" to the end of the list in the "Startup parameters" option;

Step 4: Click the "OK" button and restart the SQL Server Instance ./p>
NOTE: Make sure there is no space between ";" and "-m", the registry parameter parser is sensitive to such typos.

Step 5: After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility. Use SQL commands as follows to add an existing login (or a newly created one) to the sysadmin server role.

The following example adds the account "test" in the "UNLOCKER" domain to the SQL Server "sysadmin" role:
EXEC sp_addsrvrolemember 'UNLOCKER\test', 'sysadmin';

Step 6: Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance.

Step 7: Login to SQL server with the windows admin account. In Object Explorer, open Security folder, open Logins folder. Right Click on SA account and go to Properties.

Step 1: Type a new SQL SA password, and confirm SA password reset.

To conclude,when you forgot SA password, no need to be worried, just use MS SQL Server Password Unlocker to simply change SA password, or access to SQL Server by the more complicated single-user mode.

Related Articles