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://blog.sqlauthority.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
“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.”
Why are you suggesting a user restart the service for a simple password change? This isn’t the Windows service account being changed.
This is an unnecessary and misleading instruction, I believe.
-Chris
also, why the heck would you enable this functionality from visual studio?
this should only be possible throught sql management studio, dba’s not developers need to be able to change the sa password what the heck!
Yeah, I know this doesn’t help, but jeff, you keyboard commando, what’s the address of your blog? It’d be my pleasure to rip you to shreds for trying to help too!
Jeff, You even did not read the article.
Dave, contact me, I need your help.
Regards,
Wilson Gunanithi.J
Incorrect; you only need to restart the SQL Server service upon changing the authentication mode. Changing the sa password only does not require a restart.
Yes, it’s a good idea to test, but that’s not where the error is within your post.
Cheers,
Chris
good work perfect for me !
10x
it doesn’t matter if you restart the services including the sql server management studio although it only needs to restart only its services, I see no Fatal drawback in doing this,the writer just want to play it safe since there are many possibilities that might arise. So if you think you don’t contribut anything to this writing , so stop debating.
Thank you
Before just dismissing a comment from someone like Chris and assuming you know best, I’d suggest you take the time to try what he said. Chris was 100% correct with his comment. There has never been any need to restart the SQL Server service after changing an sa password (or the password of any SQL login).
I have to agree with what Chris and Greg state, it is not required to restart the service accounts.
When you write in your article ‘Make .sure that you restart the services …’ the reader understands that it is a necessary to restart the services (resulting in making that production server temporary unavailable, which might affect the work of innocent users who are connected using a different logon or even authentication mechanism.
If your ISP would follow your article, it would bring every website that they host down.
Personally I would extend the article to also include that the password, given your screenshot, needs to match the local computer policy and thus depends on password history and policy requirements.
Regards,
Dandy Weyn
Hi Dave
Pleased to hear that you’ll “extend the article”. But will you correct the incorrect advice regarding restarts as well? I think it would be a good idea.
Cheers,
Steve.
Hey guys,
I do not understand the big deal about restarting server. He said to test it, if you wish. If you do not want to test it that way, do not. Just do not make big deal of this. You guys were not there to say when he did things right. Just pointing out this little thing, which you do not agree.
I was looking for this solution for long time and I find it very useful.
Dave, do what you do best. It is your blog and you rock man!
Mike W
@Mike – Dave did say it was required, not “if you wish,” which is factually incorrect, but an easy correction.
In the past, I’ve recommended this blog to my SQL students, but can’t do so if errors are found that resist correction. So to me, it’s a little thing.
err, not a little thing. Whoops :)
Hi Chris,
If you will stop recommending this site to your student for this little thing. I will doubt you as teacher. How much good stuff Dave has written on this blog and see how much information this blog has.
Dave always published all the comments, no matter if they are in agreement of him or not. He himself says, readers will take decision after reading his post and comments.
Now, what kind of your students are there – who will read your comments and ignore it.
You are sure denying the God because he did not full fill your wish.
Mike W
@Mike:
I think you’ve taken it to extremes here “You are sure denying the God” ? Come on now!
And Dave did mention “it is best practice”, I would like to see the KB article or white paper where it states that is SOP for production servers.
And as far as Greg, Dandy and Chris … those guys are authorities in SQL Server.
Just my thoughts
Enrique
Dave,
The last paragraph of your post says “make sure to restart the SQL server”. I personally would read that as a “you must do this”, not just something you think is good to do but not required.
Chris and Dandy are true authorities on SQL – their posts probably make up about 90% of the MCT SQL newsgroup! – and they are correct in saying that the SQL server does not need to be restarted if you change the SA password. A service password, yes, but not an internal SQL account password.
Regards,
Peter Lee
(MCT, MCITP, MCDBA etc.)
I am following up on whole conversation. I do not agree with Mike’s tone. By saying that I want to point out that he has some valid points.
If there are authorities here in SQL they should encourage Pinal for this awesome blog and participate on other posts as well. Instead of that they are focusing on this one point. Do not you guys (authorities) think that they should read other posts and post their comments there as well instead of playing this “I am authority” game.
NG.
(I will not list my credentials here, as I do not need them to express my views)
Good one.
Thanks you helped me a lot. A LOT A LOT!
Boy has this all gotten out of hand. My intent here was not to stir up a hornet’s nest! There was (and still is) an error in the initial post – restarting the service is not required and should not be a recommendation. However, this shouldn’t have degraded into such a mess.
We all seem to share one thing – a passion for SQL Server. Let’s respect that common theme, and let this drop.
Seriously … C, G, D, E….(Names Modified)
If you Knew SQL Server so well.. you wouldnt be reading these articles on forums instead would open your own and accept all the questions and try to reply them.. Instead of making accusations on Blog Owner who usually write what they practice.. If you dont wanna follow just dont.. youknow its wrong for you to do.. then dont do it.. dont try to prove people wrong here.. learn your stuff and move on…Looks like you guys have a lot of time spending on showing you know more than others.. Move on Guys… there are better things to learn instead of proving others wrong and yourself right…
I read Dave’s article I found it a good source of information .. I am an SQl Server DBA too but I didnt get stuck to ONE STATEMENT that he thinks should be necessary to do.. according to what he practices.. you dont have a Gun pointed on your head … nobody is forcing you to do it… this is ridiculous .. to prolong this argument just to prove yourself right…
(One statement Removed)
And also .. Dave never claimed he created MS SQL Server and Knew all about it.. SQL Server 2005 itself has many bugs… few of them I identified too.. Big Deal !!!!
Has anybody tested the theory what is being claimed?
I changed password and I was still able to login using my old password while new password should be active.
I restarted the services (took less than 1 second for my monster server) after that new password was effective.
Pinal you have been correct all along.
And again, our servers created not a single second timeout in application as the time to restart services is very quick.
Honestly, not a big deal. Anybody who has experienced as DBA for 2-3 month would know this. You need not to be expert to know that service restart is very very very very quick and works well.
Mahi
)
If you think you will not suggest this site to your student. When they will find it themselves they will find out how good teacher you have been, keeping them away from such a knowledgeable site just because of authority of ego.