SQL SERVER – Change Password of SA Login Using Management Studio

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 (http://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.

About these ads

245 thoughts on “SQL SERVER – Change Password of SA Login Using Management Studio

  1. “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

  2. 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!

  3. Hello Jeff,

    Your foul language is removed from your comment.

    This is only available from SQL Server Management Studio. I have not mentioned any where that this will enable it from Visual Studio.

    I request you to read the article carefully. It will help you to understand the use of this feature.

    Additionally, you do not have to follow the instructions if you think this is not for you. This blog is for SQL Server community who wants to learn new things everyday.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

    • Hi Dave,

      I pulled a bad and loaded a database to my SQL server that locked my sa account. The problem is that no account with windows authentication has the permissions to alter the sa account, so I cannot unlock it, or change the sa password. Any ideas?

  4. 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

    • 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

  5. 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).

  6. Hi Greg,

    I have never said I know the best. I appreciate every comments and suggestions. I just have wrote my own preference and I have let all the comments published here. This is the only way healthy conversation and community is built.

    I thank you for your opinion as well every readers suggestion. I try to learn from each comment.

    Kind Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  7. 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

  8. 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.

  9. 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

  10. @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.

  11. 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

  12. @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

  13. Hi Enrique,

    Please calm down. I respect all my readers.

    If not restarting the server is good suggestion, I do agree that it can be an option. My suggestion was what I think is good to do, but not required.

    It is not about who’s who, it is about community participating together. I request all my readers to stop accusing each others.

    My articles are my personal book marks and attempt to learn new things everyday.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  14. 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.)

  15. Hi Peter Lee,

    I have never denied Chris and Dandy’s authority. All readers comments on this topic makes this as complete blog.

    My readers will read everything and take decisions.

    I also appreciate you participating here.

    Let me know if you think anything else needs improvement.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  16. 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)

  17. Ng,

    I understand what you are trying to say. Please refrain from using curse words. I have edited your comments and removed flames towards other respected members of this community.

    Authority and power comes from experience and knowledge – it makes people humble.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  18. 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.

  19. 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)

  20. 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 !!!!

  21. 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
    (http://www.sqlqa.net/)

  22. 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.

  23. Mahenra,

    I see no point discussing this topic as Pinal has posted update in original article. As well, people are unnecessary stuck on this one topic. Everybody just try to see other articles, they are EXTREMELY WONDERFUL.

    Pinal Dave Hats Off to you. I accept you as authority.

    Ronald (MCITP, MCSE)

  24. Mahendra asked “Has anybody tested the theory what is being claimed?” I have done so. I changed the sa password and could immediately logon with the new password and not the old one. I repeated the test and obtained the same results. At no point did I restart the SQL Server service or any other service. This conflicts with what Mahendra reported.

    I’d like to put this issue to rest as quickly and constructively as possible. To that end, I ran the following query and tested the password change:

    select @@version

    This revealed I am running:

    SQL Server 2005 SP2 Developer Edition (64-bit) version 9.00.3054.00

    Let’s get some other people to follow the steps described in the article and report the results along with the SQL Server version being used as report by the above query.

    Just to make sure I am not misinterpreted, I am stating that after changing the password, SQL Server should not be restarted.

  25. Hello All,

    I already have posted update in original article few days ago. Please read that before posting comments.

    I have never said new password will be only effective after restarting the services. It will be effective right away. I just have asked to do additional tests.

    I request all the SQL experts to read my other articles and participate as well. Your opinion is very valuable to me and this community.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  26. Unbelievable the comments and posts from these people. Here’s my suggestion…don’t restart sql. Here’s my second suggestion…restart sql. Take your pick and move on.

  27. Hi Pinal Dave,

    This is Srikanth ,i am working with sql server 2005 studio i

    want 2 work with sql server authentication mode so i tried to

    change the password for sa but i am getting error when i am

    changing the password for sa login id , And i cant create a

    new login id also it is also becoming the same problem ,can u

    tell me wat to do

  28. Hi Dave,

    I have one query…I have installed reporting and analysis services on my server. I dont have management studio as well as database service installed….How can I change my ‘sa’ password..Any help would be appreciated..

    Thanks

  29. Hi Guys,

    It is the best practice to restart the SQL server service whicle you change in the server level change.

    Changing the SA password is also comes under reconfig SQL server configuration.

    Microsoft itself recommanded to restart the SQL server, once you done with your reconfig on the server.

    Thanks,

    Manoj

  30. hi there, i did everything u mentioned step by step, , when i go to log in as sa, it doesnt even show on the drop down bar (no user), so i just write it in, and then write the new password and it gives me the error 18452,so i logged in with windows authentication and i see everytime i change the password, it restarts to the default one it had since i installed sql server 2005 (which i dont know what it is) on the pc, im on windows vista…\Plz need help cz im just cursing data base at uni, and with this problem i cant go ahead with my project :(….

  31. What a dumb discussion. You should probably all have your estrogen levels checked, they seem a bit high. I will not be back, so don’t waste time resonding to me.

    Thanks for the blog Pinal.

  32. Sir,

    I have working in with sql server 2005 studio. I want to change sa users password. Also i have change password & restart Sql server by using new password. sql server open. can you tell me any other possibilities to may cause the sql server.

  33. Sql server Engine having user id sa password ranjith when i entered databases are shown. my problem how to set separate password to each database.

  34. Sql server Engine having user id sa password ranjith when i entered databases are shown. my problem when user try to open database on enterprise manager it should ask password. how to set separate password to each database.

  35. Hi All,

    I think this is good for discussing the topic with one another, but it should not hurt anyone by words……That could be fine for all of us.

  36. hi,
    this is suresh,can anyone help me..
    i am using windows vistha,.net2008 so i want to install sqlserver2008express edition.while installing this i am getting this error..”invalid loginid or you do not have permissions”..

    4times i installed still smae problem ..plz help me..

    thanks in advance…

    u r’s
    suresh

  37. Hi,
    Here i have this problem.
    I changed pass for sa from Server Management for a MSSQL 2005 server and all went ok till i tried to execute my maintenance plan.
    I get the following error:
    Login failed for user ‘sa’. [CLIENT: ]
    I tried to delete the plan and i get same error.
    I made a new plan an it worked,
    The old plan i put it in disable.
    Anyone has any idea why this happen?

  38. Pingback: SQL SERVER - FIX : Error: 18486 Login failed for user ’sa’ because the account is currently locked out. The system administrator can unlock it. - Unlock SA Login Journey to SQL Authority with Pinal Dave

  39. Well today i gone through this article as one of my friend want to Change password of “SA” well great article …
    And i also respect all the seniors who came and give there suggestions.. they been in an argue because hey all have a Zeal to make that SQL world better and helping in community in better way …
    Thanks to all of you And Pinal Sir..for this great article and comments

  40. People,

    Can we stop bickering about this subject. The information helped me out alot.

    Thanks Guys,
    ITIL v3
    Microsoft Certified Systems Engineer | Microsoft Certified Professional
    Brocade Certified Fabric Professional | Brocade Certified SAN Administrator
    Brocade Certified SAN Designer | Cisco Certified Network Associate
    Microsoft Solutions Architect,
    Microsoft Solutions Business Unit

  41. Really i am very happy for got this information from this website. I have solved my problem with in fraction of seconds.Additional things ,how to reset the sa password and how to enable WINDOWS AUTHENTICATION IN(this problem was facing when install the SQLSERVER 2005 IN WINDOWS7 OS) in sqlsever 2005.

    THANKS

  42. Hello Sir,
    I have lost my sql server 2005 password(SQL serever Authentication Mode). so i have not access my Database on Sql server . So please help me..it’s urgent…

    Thanks
    Pradeep sharma

  43. @Pradeep,

    Try logging into SQL Server through SA Account.

    OR

    If you did not remove BUILT-IN Administrator login from SQL Server. You can login into SQL Server with Windows Administrator Account.

    Once you are in SQL Server, then you can change password for SQL Server Login.

    ~ IM

  44. Hi,

    New to this but my sql authenication passowrd seems to default to an unknown passowrd even when i change it why is this?

  45. Hi pinal ,

    i am in beginner level
    I love all your articles, most of them are very helpful.
    Thanks

    Comment about the comments:
    Really the comment section looks like a debate.

    hope every one may make comments to have some fun stuff and make the blog very interactive.

    Cheers to everybody.

  46. Hi Dave,

    I am not a beginner and I still find tips on your blog that I did not know. Thank you for the time you are spending sharing those tips with the rest of us.

    Regards,

    Pascal (from France)
    La critique est facile, l’art est difficile.

  47. F.Y.I

    My personal point of views.

    Mr.Dave’s SQL Authority sites offered MS SQL Server, database, and computer’s knowledge and skills for me to learn and continue updating my IT knowledge and skills.

    Sir,
    Thank you for your dedications and passions.

    Just my two cents.

  48. Hi Pinal,

    Your valueable posts are so helpful.

    Thanks for spending your time in helping us to learn more.

    Regards,
    Bhanu Prasad M

  49. Hello Sir,
    I m a neophyte , sir as per your instruction when ever i changes the password and restarts the ms sql server 2005 i does’nt take my password and on checking the properties using windows authentication i get’s the old long string coded password . please reply my query soon i m waiting.

    Regard:
    Thank you

  50. Great thread! All the silly comments. Great that the author didn’t change the article, but just updated it. You got to love the arrogance in that lol!

  51. Thank you splinter,

    Again, there is no arrogance at all, I only change my article, if there is some serious typos or incorrect links.

    I believe that anything you put on web stays there, it can not be modified or removed. All update should be clearly visible.

    Kind Regards,
    Pinal

  52. Hi Pinal

    I need a help from you. I am in the middle of Auditing of the IT Systems as this is a Financial Institution. They are asking us to remove (Uninstall) the SQL Server Management Studio from the Production Servers. The Auditors claims that removing the SSMS from Production Server is the Best Practice. My argument is that we need SSMS in the Production Server and that is the Best Practice. Can you please advice and also point me to any document that validates my point.

    Cheers,
    Jagadesh.

  53. Dear Pinal,

    Thanks for this nice article. I followed this for resetting “sa” password in SQL Server 2008. It worked fine for me even without restarting any services.

    Thanks again for your good article. Hope it helps many.

    Cheers..

    Remya

  54. Pingback: SQL SERVER – Forgot the Password of Username SA Journey to SQL Authority with Pinal Dave

  55. Hi Pinal,

    Wonderful article. Irrespective of all the unnecessary criticism, please carry on the good work. I always look forward to your blogs for help.

    Thanks,

    Ritesh

  56. Hi Pinal,
    After carefully reading this article, I have a strong feeling that you are correct. I have also faced the same problem as follows which requires restart of SQL server after changing the SA user password. Steps to reproduce the issue.

    (1) Install the SQL server EXpress 2005. When you install it st the authentication mode to windows Authentication only.
    (2) I would like to explore how WPF,WCF application is desinged in detail so I downloaded stock Trader application which is built using WPF,WCF and SQL Server or Oracle which is available at microsoft site.
    (3) When I start installing the application, it prompts to setup the database for sql sever which requires SQL server authenticaiton with SA username.
    (4) I changed the authentication mode, both sQL server and Windows. I restarted SQL server. I though SA username’s default password is blank. SO I didn’t the password at this time.
    (5) I switch back to installation of the application where it is waiting to restore the database at particular database server which on local machine in my case.
    (6) I have specified sql instance name and try to test the connection but it failed saying SA user is disabled.
    (7) I went back to SQL server, I enabled the SA user. I restarted the sQL server.
    (8) I switched back to application, I tried to test the connection. It prompted for the message that connection failed for user SA.
    (9) I changed the password of SA user and tried it again. It prompted for the message “Connection failed for the user SA”.
    (10) I restarted the sql server and tried it agian. It successfully restore the database.

    I think you are right. It is thumb rule to restart the Server when you make changes to system related configuration.

    Pinal, you are rocking!! SQL authority solves so many problems for so many DBAs, developers and architects.
    Go Pinal!!!

    Thanks and Regards,
    Samir

  57. Dear Sirs,

    I have upgrade SQL server to 2008, then use sa to login with default blank pass, can not.
    I use SQL Server Management Studio to change sa pass, but new pass can not use after stop and restart SQL services.
    So I can not change sa password, actually I follow the same with your instructions. Use sa for login with new pass impossible.

    Pls help!

  58. 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.

  59. 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

  60. 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.

  61. 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 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

  62. 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.

  63. 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?

  64. 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

  65. @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.

  66. 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.

  67. 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!)

  68. 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.

  69. 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

  70. 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

  71. 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?

  72. when ever i create new data base with the user name:sa

    and password:sa .

    it gives error message drivers is not connected

    plz send answer to my email this problem

    how to create new data base in sql server 2000

  73. I have an issue in SQL 2005. I login to SQL as SA via Management Studio. Setup a new maintenance plan for a backup. Run the job and everything is fine. Here is the problem…go change the sa password. Disconnect and Reconnect to the Instance with the new sa password. Now restart the job. It fails. You also can not delete the maintenance plan. If you change the password back, it is fine again. I can reproduce this on a different servers as well. Try it on a test system.

    Here are some of the logs from the server. I do not have the logs from the job as it was blown away.

    Message
    Login failed for user ‘sa’. [CLIENT: 1XX.XXX.XXX.XXX]

    Message
    Error: 18456, Severity: 14, State: 8.

    It all points to the password being incorrect. But that is because I changed it.

  74. I changed sql server authentication at server >properties >security to sql server and windows authentication mode.
    even when ever i try to restart it gives the following error.
    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.

  75. While attempt to change sql sa password I am geeting this error create failed for user ‘sa’
    ——————————

    Create failed for User ‘sa’. (Microsoft.SqlServer.Express.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

    ——————————

  76. Thanks a ton Pinal.

    In my case I had to restart the service, as I forgot old password and wanted to uninstall our Product which uses SQL database. it didn’t accepted new password before restarting service.

    Regards,
    Swapnil

  77. Hello,
    I’ve got a big problam after changing the sa password. I can’t lon on to my ASP.Net database program. It’s a very important database program for my company. Plese help me ASAP how to solve this problam. Thank you.

  78. HI Sir,

    Here is my problem.I have installed sql server 2005 in my laptop(os::vista). After installing every thing i could not login in windows authentication.

  79. hi.
    i installed sql server 2005 prior to .net 2008. and after installation it worked perfectly . but after installing .net 2008 , sql server 2005 fail to login.
    can any one help me about this problem

  80. Hi,

    when i am trying to login in sql server 2008 with user ‘sa’, i am getting “A connection was successfully established with the server, but then an error occured during the login process, error:0-No process is on the other end of the pipe.)(Microsoft sql server, Error:233)” error.
    Can anyone tell how to reslove this….?

    Thanks in advance.

  81. Hi

    I have SQL Server 2005 and i have changed the “sa” user password. After that backp scheduled via the maintenance plan did not executed and error 18456 Seviarity 14 state 8 occcured.

    So i wanted to chenge the “sa”user password under Mainteance plan–> Manage Connection.

    Now working..

  82. Hi Sir
    I reading with your site now am so happy now your site hopefully for me and make me want to be like U
    now I have a lot problem with sql server 2005 mean that I can not change my account when I create it msm show me like this (Create failed for User ‘sopheak’.(Microsoft.sqlserver.Smo)
    additional information:

  83. and an exception occurred while execting a transact -sql statement or batch.
    (microsoft .sqlserver.connectioninfo)
    can not use the special principal ‘sa’(Microsoft sql server Error: 15405)
    So now am waiting your help and have hoplfuly for your detail about this bye Good life is for U

  84. In my SQL 2000 backup is happing every SUN & it creates new file every time. I don’t have idea it is a full backup or incremental back.

    How I will come to know..please suggest

  85. Hi Dave,

    This topic saved us a lot of work!

    We could successful set a new password

    Our configuration:
    SQL Server 2008 express and
    SQL SERVER Managment Studio 10.0

    Thank you!
    Gunther / LDL-France

  86. Hi, I have the next problem,I changed the password sa Sql 2008 when I want to login error 233, can you help me?
    sorry for my vocabulary
    from Argentina
    Thanks

  87. Hi Mr.Dave

    I want to know that how to change password of sa by
    writing a script (writing code)not by wizard way.
    can you help me please?

    shabnam
    from Iran

  88. I can log into my SQL Server 2008 instance with Windows authentication but when I try to change the sa password, I get the following error message:

    Cannot set a credential for principal ‘sa’.

    How do I solve this?

  89. i cannot open sql server authentication and window
    authentication in sql server 2005,so please tell me how
    can i open window authentication in sql server 200

  90. the method provided at the top most does not work it gives the following errors:

    alter failed for login ‘sa’. (microsoft.sqlserver.smo)

    additional information:
    an exception occured while executing a transact-sql statement or batch (microsoft.sqlserver.connectioninfo)

    can not set a crerdential for principal ‘sa’. (microsoft sqlserver, error: 15535)

  91. Hi Pinal,

    I have downloaded SqlExpress 2005 and management studio. My PC usec windows 7 OS.

    I am unable to login using neither the windows authentiaction nor the sa login.

    Please let me know how i can login.

    Regards,
    Abhilash Rao

  92. Hi,
    I have installed sql server and didn’t set sa password, I setup only windows authentication. Now I want to set sa password. What do I have to do?

    Please help me.

  93. Forgot MS SQL Server SA password? SmartKey SQL Password Recovery is a professional MS SQL Server password recovery utility that can easily help to reset lost or forgotten MS SQL Server SA password in minutes without data loss.

  94. Please help Dave. Can I set a password for one of my database in sql server 2008 R2.
    I have probs in connection string. I doubt this could be the problem.
    Thanks in Advance. Your article is good

  95. Hi Dave,

    You are article`s are so good and catchy. I am not so strong in SQL but Whenever I have a query, I refer to your articles and can make out a good conclusion as per your guidelines and suggestions. And make things work for me. Thanks for all the efforts in bringing everything concerned to SQL under one roof. And All the suggestions and posts available in this blog are of great help.

    I also have one more query, Which I faced very recently during my application development phase using .net. i.e. I am restoring backup through custom backup/restore utility. After successful restoration, Even after restarting SQL services, I was not able to run the application and it raises an error stating Invalid login Credentials. On checking the log, I found error:18456, state:14 ;severity:38.

    Can you Please help me out in resolving this by providing your valuable suggestion?

  96. Is this a way to enable ‘sa’ user without using ssms?
    because I setup an app on the client machine with sql server express edition and without ssms, so i need to enable ‘sa’ after installition.

  97. Hi Pinal,

    It was great & worked for me. In my scenario i must have to restart server to get SA password properly. I am not concerned about restarting or not but in my case it’s worked for me & i will suggest everyone to restart there server after taking downtime.

    Regards,
    Sayed

  98. Hi All,

    What Pinal said is absolutely rite! we have to restart the services, if we don’t restart the SQL Server and all its services, then all the scheduled jobs will get fail.
    So, its better to restart SQL Server and all its services

    thanks

  99. This does not change sa password in WinXP Home.Password change but when login sa messg:”The user is not associated with trusted sql server con”

  100. Hi… I need a little help, hope someone can give me a hint on this.
    I am running an SQL 2000 Server with 7 databases, 30 programmed tasks running Stored Procedures (DTS) every 15 minutes, and Oracle linked servers against which we run queries and inserts.

    Historically in this company, “sa” user has always been left with no password.
    Last week we decided to put a bit more security, so we changed every connection string in our applications so each one will use its own login user and password. The goal is to leave “sa” unused by applications, and then finally set a password for it, and use it only for internal server purposes.

    After succesfully creating logins and changing all connections strings, now I am ready to set a password for sa. I did it first in test environment, and everything worked fine (I had to re-connect the server to Enterprise Manager with sa and password, and then edit the connections inside packages to set sa password, so all programmed tasks will run normally).

    The problem is that when I did this in production server, even when I set the password for sa in all packages’ connections, the tasks will give error (Error: -2147217843 (80040E4D); Provider Error 18456 (4818))

    Any help will be appreciated

  101. hi …
    I am using visual studio 2008 for .aspx project dev using vb.net..
    VS 2008 is coming with sql server 2005 and COMPACT 3.5 too..I want to use sql server 2005…but its not connecting from VS 2008.
    SO PLEASE HELP ME IF ANY ALTERATION REQUIRED FOR IT OR WHAT TO DO.MINE IS WIN 7 HOME BASIC OS..
    and also tell me connection strings for both of the above in vb.net code…
    thanks
    pls help any one

  102. Hi pinaldave
    It is really good for me, but i want to know more if we do like this do we have the way to prevent other people to do like this and if they can do like this they will can log in our database.

    Regard
    Kuong

  103. I have an SQL Server 2008 R2 Express on my PC. This has ‘SQL Server and Windows Authentication’ mode. I am not able to change the password for ‘sa’ login. Whichever way I try, after I restart the server, the original password of 15 characters will appear. Unfortunately I do not know what that 15 character password is. As such, I am only able to login by Windows Authentication mode.

    I have the server for learning purposes. How can I change this password to a known password?
    Thanks for any assistance

  104. I am able to change the password for ‘sa’. The 15 character masked password is misleading – whatever may be the set password, we see the same mask. After changing the password, I am able to login with ‘sa’ and the changed password.

  105. In sql server I need only server authentication,or if there are two authentication there must not show show my database in windows authentication ……….Which is under sql server authentication.

  106. how can i control SA login password b’cox sql server accessed by others they know my windows password so they login easily how can control it for they are using trusted connection plz help can I control that

  107. Its always nice to see people trying out their first steps in the sql server land. So mistakes like restarting the service are forgivable, however; in the real world where your server may be in the middle of processing 100′s of financial transactions at any one time such approach is a sure-way to get yourself in trouble with your boss, quickly.

  108. Hi Pinal,

    I am in between the installation, and it does not allow me pass a window, which is the database engine configuration window.

    I have put mixed mode, and the password and the password to confirm, but I cant pass the hurdle of the text area which is the Specify SQL Server administrators window.

    When I set a username for administrator should that person be in the domain.

    should he come like domainname/username or can I put any usernames

    Thanks,

    AB

  109. me…fresher and I locked out sa account..after reading this post I could resolve login issue myself.. Thanks Pinal Dave

  110. Hi any one suggest me on this…

    In sql server authentication i forgot my pwd for uid sa..so can u tell me step by step process how to find out the pwd or else how to reset the pwd again…i dont have the sysadmin rights…With out this iam not able to connect to th e object explorer… so plz help me out in this…

    thanks in Advance…

  111. Hi ,
    when i change password to sa user in sql sever 2055 express give me this TITLE: Microsoft SQL Server Management Studio Express
    ——————————

    Change password failed for Login ‘sa’. (Microsoft.SqlServer.Express.Smo)

    Cannot alter the login ‘sa’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

  112. I never had any problems changing the sa password from the Windows command line. I always use the following procedure:

    1.) Logon to the Windows server
    2.) Open the windows prompt (cmd)
    3.) Type: osql -L to get the list of MSSQL servers near you
    4.) Type: osql -S servername -E (The servername includes “%hostname%\instanzname”)
    5.) sp_password NULL,’new_password’,’sa’
    6.) go
    7.) quit
    8.) exit

    Try the new password by login MSSQL with sa

    Note: The new password should work even without restarting the MSSQL server. Of course you can restart the server to be on the safer side.

  113. Hi can you help with creating a bat file with script
    Type: osql -S servername -E (The servername includes “%hostname%\instanzname”)
    1.) sp_password NULL,’new_password’,’sa’
    2.) go

  114. Hi,

    After changing the password for sa account and then restarting the visual server 2008 I am getting following error message .Please help me to resolve this.

    TITLE: Connect to Server
    ——————————

    Cannot connect to HOME-D9119BCA1C.

    ——————————
    ADDITIONAL INFORMATION:

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

  115. hi Pinal sir,
    Is there any way to set sequrity ID 0×01 for a user in sqlserver 2005 express edition ??
    Actually I want to disable sa and want to create a user as super User such as sa but dont want to rename sa.

  116. Hi Pinal Sir,
    Is there any way to restrict for login to SQLSERVER but not Database, I mean it I want to grant access database remotly from windows form application but dont want to grant for connect using Management Studio.

    Actually I have 3 several Client application and there is given connection string with 3 several user. there are more than 100 unknown person is accessing database via those application remotely.

    but i am was helpless when i decode my client application which were create by me using .Net Reflector, the connection string is very open. so if any person decode the application he will get the connection string and he can access via management studio or sqlcmd or ms access that i dont want.

    i want to grant access from only those 3 application and those 3 user and nothing else. but I have 2 another application for those Database with another 2 user with some extra permissions.

    thats why i am unable to create Logon Trigger.

    I am declaring some information about the structure:

    Database : 1. ABCD 2. EFGH

    User For ABCD:

    1. “Client” with no delete permission and with insert,Update,Select Permission.
    2. “Moderator” With Select,Insert,Delete,Update Permission (With column Restriction)
    3. “Admin” With All Permission but this user will not create any problem because he is the owner of the project.

    For EFGH is same.

    From “Client” or “Moderator” User’s connection string can create problem from other application but not my client application.

    Please help me

  117. You may have forgotten the most important thing.
    If you manage to login using integrated login, and managed to set the ‘sa’ password, you must also make sure that the SQL Server is running in mixed mode. In my case, I installed SQL 2008 Developer edition, paying great attention that I chose mixed mode, and provided a password for ‘sa’, but then I installed Visual Studio Professional 2010 with all features, and if you are not paying attention during setup, SQL Express is installed and configured to use Integrated Authentication only. Right-click the server after you have successfully logged in in integrated mode and change the setting of the server to mixed mode.

  118. Mehedi Hasan
    Date:20th Dec,2012

    I want to give password for a particular database. The SQL server may have 10 databases and i want to give individual password for individual database. The reason for that nobidy can enter into that particular database without knowing that particular password for that particular database.Please help me if anyone knows that thing…..

  119. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

  120. Hi All,Need help on accessing my db. Currently im using SQL Login – ‘sa’ and Windows Authentication Login to maintain my db. All this while im just login the server using the windows Authentication and totally forgot the SQL login – ‘sa’ password. FYI, the windows id were created using AD and recently we have a issue where the AD itself corrupted and could not be restore. We have to recreate the Windows login ID but unfortunately, SQL server could not recognize the same Window login ID. I try to login using SQL login – ‘sa’ but unable to do that because of wrong password (totally forgot it). Please help me to reset the ‘sa’ password.

    The method u mentioned can only be used if you have a valid Windows Authentication. Please help.

  121. Hi Dave,

    I followed your instructions above. but it seems like is not working on my end.

    Please Help.

    Got this Error Message after using the new Password

    Login failed for user ‘sa’. (Microsoft SQL Server, Error : 18456)

    Thanks,

  122. Hi Dave,

    Im using SQL server 2008 R2 and Windows 2008R2 server i would like to change both passwords.Is there SQL server 2008 R2 linked with Domine if linked it raise any error.this is live operation

  123. Hi All,
    Please me on below issue!!!!!!!!!
    I am using SQL Server 2008 R2 and Windows server 2008R2. My SQL Server Authentication setting is Mixed Mode there is windows login disabled and SA password also forgot.
    Is there any way to reset SA password please reveal me ?
    Appreciate the reply.

    Regards,
    Rajendiran

    • Dear Sir,

      Follow The pinaldave Article->
      SQL SERVER – Change Password of SA Login Using Management Studio
      You can sure reset SA password.

      Regards,
      Shashikant

  124. This was very useful Pinal. Thanks! In addition, sometimes the SA login is disabled by default in certain server installations. So, after changing password, I also had to go and explicitly enable this login

  125. Sir, namskar , when I try for log in through ‘sa’ password= ‘sh’
    my sql server 2008 not login and show the following message
    ‘Login fail for user ‘sa’ Reason: The account is Disable (Microsoft Sql Server Error : 18470)

  126. I unable to change the password, its always fire a error message : Alert failed to Login ‘sa’ (Microsoft.SqlServer.Smo)

  127. I am using sql server 2008 R2. I changed the password as mentioned in the article. I am getting below error when I am trying to login using sa and new password.

    TITLE: Connect to Server
    ——————————

    Cannot connect to Servername\Instancename

    ——————————
    ADDITIONAL INFORMATION:

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

  128. i am getting error that can not alter sa pawword

    can not set the principal for creditial ‘sa’
    i have also try to stop sql service but it is not showing any sql service running in congigration manager.insted of service it is showing error” remote procedure call failed”

  129. Dear Pinal,

    I follow the same process but getting below mention error kindly me asap , i done on my very important Db. I restart the services after that i m getting error
    TITLE: Connect to Server
    ——————————

    Cannot connect to SID-PC\SQLEXPRESS.

    ——————————
    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

  130. i want to change setting of sa password from strong to normal or weak .
    i want to set password as “abcd” simple password . how to do it ?

  131. Hello Pinal Sir. I am a new DBA. very very new and facing lots of problem everywhere. Any way..I forgot my sa passwword and thanks to your post i reset it.

    I have another question. I have installed second instance on my laptop. and also lost its id and password. How to get that?

  132. Pingback: SQL SERVER – Reset SA Password – SQL in Sixty Seconds #066 | Journey to SQL Authority with Pinal Dave

  133. Whats up very nice site!! Man .. Beautiful .. Amazing .. I’ll bookmark your web site and take the feeds additionally? I’m happy to find so many useful info here in the put up, we want work out extra techniques in this regard, thanks for sharing. . . . . .

  134. 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.

  135. 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

  136. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s