SA (System Administrator) is the administrative login for MS SQL Server. All the password in SQL Server are stored as a hash in the master.mdf file.
But the problem occurs when you forget the SQL Server SA password. You may thought that their is only option to reinstall the SQL Server and reattach the database. In this article we will discuss the several methods to recover SA password in SQL Server.
How to Recover SQL Server SA Password?
There are many methods to recover the lost SA password in SQL Server database. Some of them are given below:
- Using command prompt.
- Using SQL Server Management Studio.
- Using SQL Server Configuration Manager.
Resetting the SA Password Using Command Prompt
Follow the simple steps given below to easily recover the SA password in SQL Server database with the help of command prompt.
- Go to the command prompt and type osql–L.
- Now copy the full name of SQL Server and type OSQL -S < insert_servername_here > -E.
- After doing this you need to run this query sp_password NULL, ‘< insert_new_password_here >’, ’sa’.
- And type Go and execute the query.
Recover SA Password using SQL Server Management Studio
To reset the SA password with the help of SQL Server management studio you just need to perform the simple steps given below:
- Go to Start -> All Programs -> Microsoft SQL Server -> SQL Server Management Studio.
- Connect to SQL Server instance via Windows Authentication.
- Navigate to Logins object under the Security > right-click on the SA user and choose Properties.
- Now, enter the new password in front of Password and Confirm Password field respectively and click on OK.
Reset SA Password Using SQL Server Configuration Manager
Follow the given simple steps to recover SA password in SQL Server via SQL Server Configuration Manager:
- Open SQL Server Configuration Manager.
- Stop the SQL Server Instance for which you need to reset the SA password.
- Open Properties on the SQL Server Instance and click on the Advanced tab.
- Change the Startup parameter by adding -m; at the beginning of the line and click OK.
- Start the SQL Service Instance.
- Open the command prompt.
- Run sqlcmd and press enter.
- Run a T-SQL command to add an existing account or a new account to the sysadmin server role. Replace DOMAIN\Username with the account you want to add.
In this article we have discussed the methods that can be helpful to recover SA password in SQL Server. But sometimes these manual methods failed to recover the highly secured passwords. At this time you can take the help of third party SQL Server SA password recovery tool that easily recover the SA password.