It might be possible that you forgot the password of SQL Server for SA login and you want to get it back anyhow. This can be happen because of not using SQL server for while and maybe password was too complex so it was not easy to remember. Whatever There is no way to get the password back but you always have a chance to change or reset it. In this article, you will learn different ways to create a new password for SA. You can change or reset the password using SQL Server Management Studio, SQL Script or using command prompt.
Way 1: Change SQL Server password using Management Studio
Using SQL Server Management Studio is the easiest way to change the SQL server password. To change the password, you have to select “Windows Authentication” as authentication instead of “SQL Server Authentication” because you are not able to login in SQL Server Authentication.
Follow the below steps one by one to change the password :
- Open SQL Server Management Studio.
- Once SQL Server Management studio windows appear, select “Windows Authentication” from the dropdown list as Authentication.
- Click on Connect button.
- Once connected successfully, Go in View menu from Menu Bar and select “Object Explorer”.
- Expand “Security folder” appearing in Object Explorer.
- Expand the “Logins” folder appearing inside the “Security” folder.
- Right, Click on “sa” appearing in the list and click on Properties.
- Once Login Properties appears, select General appears under “Select a page” from the left side of the window
- Enter Password and then retype the same password in Confirm Password.
- Click on the Ok button.
- Now Re-connect SQL Server Management Studio again with SQL Server Authentication.
Way 2: Change SQL Server Password using SQL Script
If you prefer to write a script instead of going with way 1, you have to use the below script.
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
ALTER LOGIN [sa] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
— NewPassword, write whatever password you would like to change.
Way 3: Change SQL Server Password using CMD(Command Prompt)
Follow the below steps to change SQL Server password using the command prompt.
- Open Command Prompt as an Administrator.
- Enter command, Type OSQL -L and press Enter.
- Enter another command with your server name, Type OSQL -S server -E, where you need to replace server with your server name and then press Enter.
- Now create a new password, To create a password you have to type command EXEC sp_password NULL, 'password', 'sa' where “password” must be replaced by the password that you would like to use, then press Enter.
- At last, you need to execute the command. Type Go and then press Enter.
In case of any query while creating a password, Please feel free to comment here with issues in details. we will try to resolve it as soon as possible.