none
How to unlock the default sa account RRS feed

  • Question

  • Hello,

    I am having problems with changing or unlocking the sa account under SQL Management Studio 2012.  We have the right password now for sa but can't unlock the account at all.  I have tried using two different administrator accounts via Windows Authentication that I would've thought would allow me to unlock the sa account but they did not work.  Here is what else I have tried:

    I get a "Reset password for the logon while unlocking" message when I click off the "Login is locked" checkbox under the sa login properties.  When I attempt to change the password I am told I do not have permission to do so.

    Have tried running ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

    Those did not work.  I am about to try a 3rd party software but wanted to see if I could save the money.  Would anyone have a suggestion or some other possable way to unlock the account?  Please and thank you!

    Thursday, August 8, 2019 2:33 PM

All replies

  • Does your Windows Authentication administrator accounts have the server role "sysadmin"?

    A Fan of SSIS, SSRS and SSAS

    Thursday, August 8, 2019 4:17 PM
  • Hi CentralPA1,

     

    >>I have tried using two different administrator accounts via Windows Authentication that I would've thought would allow me to unlock the sa account but they did not work.

     

    Administrator accounts have permission to unlock the sa account. Would you please check if the administrator you use have the sysadmin role:

     

     

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 9, 2019 6:59 AM
  • What means "did not work", do you get an error message (which one)?

    Is SQL Server running in mixed mode or in "Windows Login" only mode? With last one you can't use SQL Login "sa".


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 9, 2019 7:22 AM
  • The accounts I tried were my own Server admin and even Domain admin accounts created in AD.  I would've thought surely one of those would have sysadmin privledges but I guess not. When I login via Windows Auth on my sa or da account I click the Security -> Logins pull down and all I see is BUILTIN\Users and the default sa account (the account im trying unlock).  My own .sa account is not shown.  Hopefully that made sense.

    If I try and create a different sa account within SQL and assign the sysadmin role to it that will error with a "do not have permission" message.

    Friday, August 9, 2019 1:21 PM
  • Hi Dedmond,

    I am not well adverse with SQL so please bare with me.  What I have been trying to do is use my own .sa or .da accounts that were created in AD.  After I login to that and look under the logins list my .sa nor my .da account are listed.  Just BUILTIN\User and the SQL sa account.

    I have tried creating a Login account with my AD .sa account but that keeps saying I do not have permission to perform this action after I select the sysadmin checkbox.

    Friday, August 9, 2019 1:30 PM
  • When I attempt to unlock the sa account from my Windows Authenticated AD server admin account I keep always getting "User does not have permission to perform this action" messages. Samething happens when trying to create another sa account.

    It is running Windows Auth mode because the sa authentication (or I am guessing mixed mode) is locked out.

    Hopefully that makes sense.  I am about as novice with SQL as it gets.

    Friday, August 9, 2019 1:33 PM
  • Go to the SQL server Properties page, you can see if the server authentication is in the mixed mode.


    A Fan of SSIS, SSRS and SSAS

    Friday, August 9, 2019 2:07 PM
  • It is in Mixed mode (exact same setting as the image you posted)
    Friday, August 9, 2019 2:45 PM
  • Is your Windows Authenticated AD server admin account listed in the Logins under Security? If not, try to add it and then make it as a sysadmin in Server Roles.

    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Friday, August 9, 2019 3:38 PM
    Friday, August 9, 2019 3:37 PM
  • The accounts I tried were my own Server admin and even Domain admin accounts created in AD.

    Your domain / windows permissions doesn't matter in any way here, SQL Server has it's own permission system independent of OS permissions.

    See Connect to SQL Server When System Administrators Are Locked Out for how to get admin access.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 9, 2019 4:03 PM
  • Well that stinks. I'll take a look at the link you provided. Thank you Olaf!
    Friday, August 9, 2019 4:42 PM
  • I did, it told me I do not have the permissions to perform such action.
    Friday, August 9, 2019 4:42 PM
  • If you get that error message, then you don't have admin permissions on OS level

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 9, 2019 4:59 PM