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.
Make sure to restart the SQL Server and all its services and test new password by log into system using SA login and new password.
Reference : Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)
UPDATE : There has been discussion about restarting the SQL Server and all its services. Please read all of them before making final decision for your scenario.
262 Comments. Leave new
Dear all,
Today I am lucky,
I have finished changing sa pass in SQL server 2008.
First, to enable sa account by right click on data –> properties –> permission –> search –> browse –> sysadmin.
Then I change pass for sa.
Then stop sqlserver…
Then restart it.
Thanks everyone have comments.
Hi Dave
Thank you for your excellent explanation. I did as instructed and it worked perfectly. Funny enough, I took from your post what I required, how to change the password, and I stopped there. So I didn’t do a restart of services.
So I guess I came as a bee to the flower and went away making honey…
Some users came as spiders to the flower and went away making poison… this is most unfortunate… and I think defies the quiet purpose of your ideal for this blog… be not discouraged by this.
Let your articles flow as there are many bees looking to make honey…
I have you now bookmarked as my favorite authority on MSSQL matters.
Keep Well and THANK YOU!!
Warm Regards
Jaun aka Webfreak
my problem is not how to change the password but how to log on for the first time on MS SQL management studio server on a standalone computer.
Hi Pinal,
How can we maintain separate passwords for separate database in SQL 2005?
Regards,
Dileep.
Hi Dileep,
SQL Server check authentication at server level not at database level. It means the password is checked while connecting to server, not while connecting to database.
To keep saperate password for each database, you will have to create saperate logins and map them to saperate databases.
Please let me know if you need more details.
Kind Regards,
Pinal Dave
What if I can’t log on? How can I change the sa password? I am a sysadmin. I think I forgot the password. My colleague couldn’t log on. Is there a way to reset without loging in? Is there a powershall command to reset?
Hi James,
Are you not able to login with windows authentication?
You can’t change SQL server settings without logging into SQL Server.
Regards,
Pinal Dave
Hi Panel,
An Urgent help required.
I’m able to login to SQL server 2008 using windows authentication.
while trying to change the password of SA , I get an Error Msg which Displays:
Change Password failed for login sa
An Exception occured while exceuting a transact-SQl statement or batch.
Cannot alter the login sa, because it does not exist or you do not have permission.(Microsoft SQL server ERROR:15151)
Please could you help me with this.
Thanks,
Shilpa
thanx…
Thanks Pinal
Hi, Dave.
I am a 3 months old new SQL DBA. Today one of my colleagues could not log on using sa account and I couldn’t remember the password either. I came home frustrated and burdened.
Then I searched the Google and found your website. I tested changing the password for ‘sa’ in both ways and they worked. I thank you for your article. It’s useful and I appreciate your sharing.
But I’d like to know one thing. After changing the password what impact does it have when I restart SQL Server? As a beginner I am afraid to restart SQL Server esp. in a production environment.
Hello James.
When you change password for any SQL account SQL Server service will not be affected. And it is not at all required that you restart SQL Server after you change password for SQL Account.
BUT, if you have used this SQL Account in any of your application and if you have hardcoded password for this SQL Account, then there is serious problem, because the other application will not work, it will fail because you have changed the password.
So, if possible first Identify where all this SQL Account has been used in all applications so that when you change password for this account, you can go and change password at all applications, where all you have used this account.
~ IM.
I found that the server I tried to change the sa password, no one has been able to log on.
I see this error message,
“Login failed for user ‘COMP\JPARK’. (.Net SqlClient Data Provider)
——————————
Server Name: BPXXXXXXX
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
Is there a way to log on to this server?
Thanks for this info, I really enjoyed reading it!
Thanks for the Information provided…
Hi Dave,
I read this article and as per your instructions I changed password for SA and restarted sqlserver, eventho it showing some othe password.
I set password as me, after i see the password and it was showing as ……
Please advice.
Thanks,
venkat919
@venkat
Do you able to see the password in text, if not then password is changed to whatever you write SQL Server always shows more or less dots than orignal password lenght.
So please don’t bother with the lenght of dots your password is changed i think.
Give it a try.
Hi pinal, I have changed the password for the use : sa but I am not able to login with password nor even able to restart the sql server. when I am trying to restart the server it gives me error “Unable to stop service MSsql$sqlexpress on server server_name”
IN additional information it shows that : Access denied(Microsoft.sqlserver.express.objectExplorer). will please suggest me where I made mistake.
My os is Widows vista. and sql server 2005 I am using.
Thanks a lot for the article, it worked like a charm. (Although I have to say that, using SQL Server 2008 with Windows 7, I had to restart the server in order to get the new password working!)
i am developing a software on my laptop using C#/SQL Server. it is running well but when i run it on another PC with the same SQL Server version (2000) and same configuration, it is saying that “login failed for user PC/PCuser”
Any help please!
Are you using SQL authentication with username and password? Could it be that you’ve forgot to add those login credentials to Sql Server and/or database? Or that you haven’t enabled SQL authentication on the server.
I have this error while trying to login
“An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider)”
using sa login.
I am running SQL Server 2008 on MS Server 2003. the login is fine until we used hiren CD to recover Administrator login.
If I try to login using Windows Authentication, I get the same error. Any help is highly appreciated…. thanks
Hi Dave
I have followed your instruction to change the SA pwd. It works great. Thanks for the information.
I am running SQL Server 2008 Express on two machines(both are Win XP Pro). In one server I have the Windows authentication enabled for connecting to the SQL server and in other I have the SQL Server Authentication enabled.
Now I want to export a database(with all the Tables, Data etc) from Windows Authentication enabled SQL server and import into the SQL Server Authentication enabled SQL Server. Could you please tell me the procedure to perform this action?
Regards
Jossy
Hi Pinal dave,
I want to know if one can change the sa password using windows authentication, then is it necessary to be logged in to windows as a ADMIN or any user can have an access to the SQL Server and can change the password?
If any user can change the SA password using windows login then that user can all also change the database records too…
So is there any other way where in which a security is being maintained and only the DBA can change the password?
Can u please suggest on this?
Can u please tell in detail?