To change password of SA login in Management Studio, follow these steps. Login into SQL Server using Windows Authentication.
In Object Explorer, open Security folder, open Logins folder. Right Click on SA account and go to Properties.
Change SA password, and confirm it. Click OK.
The same thing in one line of T-SQL
Clicking is fine once. If you are doing this on twelve servers, or writing it into a runbook, use the script.
ALTER LOGIN sa WITH PASSWORD = 'YourNewStrongPassword'; GO
If a policy requires you to prove you know the old one, or you want to force a change at next login, both are available.
ALTER LOGIN sa WITH PASSWORD = 'NewPassword' OLD_PASSWORD = 'OldPassword'; GO
About restarting SQL Server
The original version of this post said to restart SQL Server and all its services afterwards, and there has been a lot of discussion about that in the comments over the years. Here is where I have landed, plainly.
Changing the password does not need a restart. It takes effect immediately. Sessions already connected stay connected, because SQL Server checks the password when you log in, not continuously.
What does need a restart is switching the server between Windows Authentication and Mixed Mode. That is a different change, and it is probably where the advice came from originally.
So on a live server, do not restart out of habit. It costs you every connected user for no benefit.
Changed the password and still cannot log in
This is the most common follow up, and there are two usual causes.
The server is in Windows Authentication mode only. In that mode SA cannot be used at all, whatever its password is. Check it, and change it if you must:
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly;
-- 1 means Windows only, 0 means Mixed ModeTo switch to Mixed Mode: right click the server in Object Explorer, Properties, Security, choose SQL Server and Windows Authentication mode. This one does need a restart.
The SA account is disabled. SQL Server disables it by default when you install with Windows Authentication, and a new password does not enable it.
SELECT name, is_disabled FROM sys.server_principals WHERE name = 'sa'; ALTER LOGIN sa ENABLE; GO
Nobody can get in at all
The worst case: SA is locked out, and whoever set the server up has left the company. You are not stuck, as long as you are an administrator on the Windows machine.
Stop the SQL Server service, then start it in single user mode with -m from SQL Server Configuration Manager or the command line. In single user mode, any local Windows administrator connects as a sysadmin. Connect, add yourself or fix SA, then stop it and start normally.
One warning that catches people out: single user mode allows exactly one connection, and Object Explorer quietly takes it for itself. Connect with a query window only, or use sqlcmd, otherwise you will be locked out of your own rescue.
A word on whether to use SA at all
Since you are here changing its password, it is worth saying. SA is the most guessed login name on any SQL Server, and it holds every permission there is. Every brute force script on the internet tries it first.
The safer setup is to leave SA disabled, give real people their own Windows logins, and give each application its own login with only the permissions it actually needs. Then a leaked password costs you one application rather than the whole server.
If you must keep SA enabled, at least rename it, because that alone defeats most automated attempts.
ALTER LOGIN sa WITH NAME = [AdminAccountName]; GO
Test the new password by logging in with SA before you close your working session. If something is wrong, you still have a way in.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
262 Comments. Leave new
Hello there, You have done an excellent job. I will definitely digg it and personally suggest to my friends. I am confident they’ll be benefited from this site.
Its very helpful for us thank you
Hi,
Plz don’t criticize our Pinal Dave, he is our guru & master of many SQL Server users.
We are the followers of Pinal and we should respect his dedication and helping mind.
A true talented SQL DBA like him comes once in the world with helping mind.
We should restart the server after any changes in the server level.
It is the best practice of Microsoft product. It improves the server performance and avoids unexpected error in the future.
Feedbacks are always welcome in his post, but it should not destroy our relationship.
Do you confirm it works better without restart the server?
Thanks
G Arunagiri
I usually do not leave a ton of responses, however i did a few searching and wound up here SQL SERVER – Change Password of SA Login Using Management Studio . And I actually do have a couple of questions for you if you tend not to mind. Is it only me or does it appear like a few of these responses appear like they are written by brain dead folks? :-P And, if you are posting at additional social sites, I’d like to follow everything fresh you have to post. Would you make a list of all of your communal sites like your twitter feed, Facebook page or linkedin profile?
dear dave,
inspite of changing that damn sa password, still it gives me the message the login is not correct or the password is not correct. I follwed all ur instructions and it has become a big headache for me.ls. help
@paresh,
What is the errror you get in ERRORLOG? Are you sure that SQL is running in Mixed Authentication mode?
Hi Pinal,
can anyone help me with sql server. Just cant solve it. And this is the error which iam being shown:
TITLE: Connect to Server
——————————
Cannot connect to USER-PC.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘user-PCuser’. (Microsoft SQL Server, Error: 18456)
For help, click:
——————————
BUTTONS:
OK
——————————
Regards,
Devang Shah
What is the error you are seeing in ERRORLOG?
Hi Pinal,
How to extract password text of SA or other users if Auditors want to know if password is default or not.
Thanks.
Shoaib – You can’t do that. There is nothing called “default password” in SQL. You can try the trick in my blog to check weak passwords. http://blog.sqlauthority.com/2015/01/19/sql-server-how-to-find-weak-passwords-using-t-sql/
Hi Pinal,
On my server I have SQL Server 2008 R2, since the morning sa password is being reset by itself. I have found some wearied jobs and sql user names which I never created. Can you please tell me what could be the reason.
only one reason which comes to my mind first – your server has been compromised. In simple words – hacked.
hello its good explain but i have problem in each day i change sa password in the next day i fount it change by it self and iv used many inti-viruses and my server clean can you help me in that? thank you…
SQL doesn’t change password automatically. You may need to capture trace from the server to find culprit.
One of our sql servers was locked down by a former employee. I managed to reset the SA password with SQL Server Password Changer. It seems that the SA password is stored the master.mdf file.
there is a simple trick https://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/
yes i agree we doesn’t need to restart services for the change of SA password.
used to reset sa password to blank and it was working fine, but now from past couple of days it reset sa password many times in a day and each time i have to login using windows authentication and reset sa password to blank, kindly suggest me solution.
For the last few days I have been trying to change the PW to several SQL accounts including the sa account but every time I go through it like it is shown here but when I put in the new PW and hit ok at the bottom it does nothing. I’ll go back in and the password will not have changed.
Hello Dave,
I am getting following error while login with sa
Login failed for user ‘sa’. (Microsoft SQL Server, Error: 18456)
Please help me.
very useful for me to connect with SQL server thanks for your information
HI can change sa password through sql query jobs
Hi, I’m able to log into SQL Management Studio using Windows authentication and administrator login. But when I try to change the sa password, it gives me an error. I believe it is saying I don’t have the elevated priveleges to change the password for sa. I don’t really know much about SQL server. Any suggestions?
Thanks5
I figured out where the password was hidden, so I was able to log in.
Thanks
simple n very IT work .. thx