How to Reset SA Password in SQL Server Manually?

Have you forgotten or lost SQL Server password of the system administrator (SA) account? Now you cannot log in to SQL Server. If you are not able to recall the correct password, you must reset it. Through this article, you will learn how to reset SA password in SQL Server using the free manual solutions. You can also consider using an automated SQL Password Recovery tool that will give you quick and better results.

Microsoft SQL Server is the most popular database system to date. It has many features that you may not get in other database management systems like Oracle and MySQL. Being a server-based application, Microsoft SQL Server allows you to create multiple user accounts. Each user account gets a username and password to log into the SQL Server. There is also a system administration (SA) account that serves as the default administrator account in the SQL Server. The SA account is used only during the installation and migration processes.

Sometimes it may happen that you lose the SQL Server password of your user account, hence you cannot log into the SQL Server. But there is no need to worry as long as you people have access to the SA (system administration) account. Your system administrator can set a new password for your user account. That is so simple. But things might get worse if you have lost SQL Server SA password. There are various situations where people forgot or lost SQL Server password of their SA accounts. In such a situation, many of you consider reinstalling the SQL Server and reattaching to the database as the ultimate solution. But there are various methods out there that can help you reset SA password in SQL Server.

Best Ways to Reset SA Password in SQL Server?

If you have recently lost SQL Server password, instead of reinstalling the SQL Server, you should try a few easy manual methods to reset SA password in SQL Server. Microsoft SQL Server uses a smart recovery method to preserve the objects and other data in a master database file. In case you do not know, SQL Server has an ability to launch in the Single-User mode. Using this mode, you can log into the SQL Server with sysadmin privileges through any account that is being a part of the Local Administrator’s group.

You can start SQL Server in the Single-User mode by using the parameter -M or -F in the startup parameters. Remember, only one user will be connected at a time while using the Single-User mode. You need to use the SQLCMD commands to connect with the SQL instance and interact with it from the command prompt.

Use Single-User Mode

Before you can use the Single-User mode, make sure the Microsoft SQL Server Management System is installed on your system. Then follow the steps shown below:

Step 1: First, open the command prompt in the administrator mode.

Step 2: Here you need to stop the SQL Instance from running. To do that, type the command net stop MSSQLSERVER and then hit the Enter button. Doing this will stop the SQL services running currently.

Step 3: Now you need to restart SQL Server in the Single-User mode. To do that, type the command net start MSSQLSERVER /m”SQLCMD” and then hit the Enter button. If you do not see anything, this is fine. When you are connected in the Single-User mode, this is not necessary that you will see any indication. Instead, you will see this – The SQL Server <MSSQLSERVER> service was started successfully.

Step 4: Now you need to connect to the SQL Server. To do that, simply type the command sqlcmd and then hit the Enter button. Doing this will open the SQL command line. Please note that if you want to specify any particular SQL instance, you need to use the appropriate SQLCMD switches. For example: sqlcmd -SServerName\InstanceName.

Step 5: Here you need to create a new user and password using the T-SQL (Transact-SQL) commands. Type the command CREATE LOGIN name WITH PASSWORD=’password’. Here, “name” denotes the account name and “password” denotes the new password. You also need to use the GO command since you are directly using the T-SQL commands and then hit the Enter button.

Step 6: Now you need to add this newly created user to the System Administrator role using more T-SQL commands. Type the command SP_ADDSRVROLEMEMBER name,‘SYSADMIN’. Note: Here, “name” denotes the account name. Then type the command GO and hit the Enter button.

Step 7: Now exit the SQLCMD command line by simply typing the command exit and hitting the Enter button. You also need to restart the SQL Server in the regular mode. To do that, cancel the Single-User mode by simply typing the command net stop MSSQLSERVER && net start MSSQLSERVER and then hitting the Enter button. When you see the message “The SQL Server <MSSQLSERVER> service was started successfully”, you can then close the Command Prompt.

Step 8: Now launch Microsoft SQL Server Management Studio using the SQL authentication and login as the new user you have created earlier. Click Connect.

Step 9: Under the Object Explorer section, click Security > Logins. You will see the newly created account in the list. The SA account is also listed there. Right-click it and select Properties.

Step 10: On the System Administrator Properties window, enter a new password and confirm it by entering the same password again. Then click OK and close the properties window.

In this way, you can reset SA password in SQL Server successfully. There are a few more manual methods by using which you can reset SA password in SQL Server:

• Use OSQL utility in the command prompt
• Use the Plesk interface (condition applied)

Use OSQL Utility to Change SA Password

Are you familiar with the OSQL utility? It is a command-line tool for SQL Server available with 2000 and later versions. Follow the steps shown below to use the OSQL utility:

Step 1: First, open the command prompt in the administrator mode. Now you need to list the SQL Server instances.

Note: Please note that the OSQL utility may not receive a response from all the servers in a timely manner just because of the broadcasting nature on networks. As a result, the list of the servers returned might be different for each invocation. So if the command is not displaying any server, you should try to execute it again.

Step 2: Now you need to type a command (shown in the screenshot) specifying an SQL Server instance for which you want to reset the password. After typing the command, hit the Enter button. Note: Replace **** with the new password.

In case if there is a remote Microsoft SQL Server instance being used, you need to use its remote server name or IP address:

Use Plesk Interface to Reset SQL SA Password

In case you do not know what actually Plesk is, it is a commercial web hosting platform having a control panel. It helps server admins set up new websites, email accounts, reseller accounts, and DNS entries using a web-based interface. If the Microsoft SQL Server instance has been registered in Plesk, you need to follow the steps shown below:

Step 1: On the Plesk interface, go to Tools & Settings > Database Servers.

Step 2: On the Tools & Settings window for Database Servers, click the hostname of an SQL Server instance whose password you want to change.

Step 3: Click Change Password.

Step 4: Enter a new password and confirm it by entering the same password. Click OK to save the changes you have made.

Professional Solution to Recover SQL SA Password

If you cannot use the above manual methods to reset SA password in SQL Server, you should consider using a professional SQL Password Recovery tool. Unlike the manual methods, it is an automated and cost-effective solution to recover SQL SA password. There are many software vendors in the online marketplace that provide such password recovery solutions, and Aryson is one of them. The SQL Password Recovery tool provided by Aryson allows you to remove the old password without knowing it and reset SA password in SQL Server. It supports unlocking both the user accounts and SA accounts.

This software uses an advanced algorithm to unlock both encrypted and unencrypted passwords without causing any damage to the original MDF (master.mdf) file. It can also recover multilingual passwords with ease. The SQL Password Recovery tool supports all major versions of Microsoft SQL Server including the latest 2019. It is available with the free demo version that will only allow you to see the list of the users (both normal users and system administrators). To reset SA password in SQL Server, you need to purchase the licensed version of this software.

How to Change or Reset SQL Server SA Password?

The SQL Password Recovery tool is an easy-to-use application that requires no prior technical knowledge. To use this software, download it from the official website of Aryson and then install it on your system. The installation is also very simple, and it does not take much time. Once you have successfully installed the software, double-click it and follow the steps shown below:

Steps to Reset SQL SA Password Successfully

Step 1: On the home screen of the SQL Password Recovery tool, click the Browse button to select the MDF file.

Step 2: On the Open dialogue box, select the MDF file (master.mdf) and click Open to confirm.

Step 3: The selected MDF file will be listed in the edit box. The software will auto-detect the version and the build version of the MDF file. Now select the desired user from the user list.

Step 4: In case you want to reset SA password in SQL Server, select SA in the list. Or to reset user password in SQL Server, select the desired user in the list.

Step 5: Now enter the new password for the SA account or any other user account you have selected in the previous step. Finally, click the Change Password button.

Step 6: Within a couple of seconds, the password will be changed. Click OK and close the software application.

Below is the working video of SQL password recovery tool:

In this way, you can reset SA password in SQL Server successfully. You can now login to SQL Server without any problem.

Conclusion

The article has explained a few easy manual methods to reset SA password in SQL Server. And if the manual methods do not work for you or take a lot of time, you should use the professional SQL Password Recovery tool recommended in this article. It is your choice which method you want to use. However, I would advise you to note down the important passwords in a diary of system notes so that you can never lose them.

Related Post