XML FeedsThere was a question today How to change my local sql server sa password? i would like to expand on my answer in this post
Before I start I would like you to read this post by ted Krueger first: To SA or not to SA to understand why you should not be using the SA account.
Now that you know why you should not be using the SA account and you are still using it, let's see how you can change the password for the SA account
The easiest way is to login to the server as sa or any other account that has sufficient privileges to change the sa account
here is what the script looks like
- USE [master]
- GO
- ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword'
- GO
Most likely you will get this error
Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.
There are two things you can do, the smarter thing would be to pick a complex password with some digits and some characters that are not alphanumeric. Or you can shoot yourself in the foot by turning the check off
- USE [master]
- GO
- ALTER LOGIN [sa] WITH PASSWORD=N'NewPassword', CHECK_POLICY =OFF
- GO
Now try to login. It is possible that the sa account is disabled, you will see the following message
Login failed for user 'sa'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)
To enable the account, all you have to do is the following
- ALTER LOGIN [sa] ENABLE
- GO
You do not need to restart the SQL Server service for a password change. One of the myths that you will hear is that you need to restart SQL Server. When you change from SQL authentication to mixed authentication and vice versa, you do need to restart for the changes to take effect but for a password change it is immediate, no need for a restart!
And if you are old school, you can also do the following, thanks to Aaron Bertrand for this
- EXEC sp_password N'old password', N'new password', N'sa';
You can also do all of this from SSMS with the GUI, navigate to the security folder, right click on the sa account, select properties and you will see the following window
Click on the Status page to enable or disable the account
I would encourage you to use T-SQL not the GUI, the day (and I promise you this will happen) will come that sql server will be installed on a box without the tools (Sever core perhaps) and you can only connect to it from the command line with osql, you will be stuck if you don't know how to do this with T-SQL
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Good post. However looking at a solution like SQL Server Core, or even some of the current versions, how would you get passed the SA password issue when you have no rights within SQL Server.
Never new that I could change passwords like that. Thanks a lot. 
When "all your DBA's are killed", you're in big trouble.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.