none
SA password

    Question

  • I accessed SMSS on production SQL server with Domain Admin credentials just to take a look on DBs.

    I need to copy one. I found that SA account is enabled and it is the only Login with sysadmin roles.

    After couple of calls "we" found the password for SA.

    I want to disable SA account and create AD auth one.

    Before doing that I want to be sure that Locking down SA login will not affect the functionality of the SQL (30 DBs).

    At least I want to change the password of SA.

    Should I expect any problems after locking SA ?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 16, 2017 12:20 PM

Answers

  • Before you do disable or changing the sa account, ensure no one nor no applications being used has explicitly.

    once you got the information ,you can disable it-there is no issues,usually as per best practise it is good to disable the SA account.

    I dont think so ,it requires any restart of services.

    if incase the sa account renamed to different incase of <=sql2005 & planning to go for version upgrade to higher may lead issues but this is out of scope of your question. so.


    Regards, S_NO "_"

    • Marked as answer by pob579 Thursday, February 16, 2017 3:51 PM
    Thursday, February 16, 2017 3:40 PM
  • David,

     I can lock (disable) SA login with no impact on the SQL server and having enabled Login with "sysadmin" role will

    be appropriate replacement. (Aware about SQL authentication vs "Trusted" (AD) authentication.

    Another related questions:

    1.Just read a blog about changing SA password. There are suggestions restarting all SQL services after changing SA password.

    One person saying that it is unnecessary.

    After your answer in previous post I feel that it is unnecessary. What would you say?

    2. Also, few people mentioned that after changing password in SMSS they cannot login (login error).

    Is it anomaly? or there is a chance for such behavior?

    Thanks!

     


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    • Marked as answer by pob579 Thursday, February 16, 2017 3:51 PM
    Thursday, February 16, 2017 3:20 PM

All replies

  • >Should I expect any problems after locking SA ?

    No.  SQL Server never connects as SA, and SA can still own databases when locked.

    David


    Microsoft Technology Center - Dallas <p></p> <a href="http://blogs.msdn.com/dbrowne">My Blog</a>

    Thursday, February 16, 2017 2:58 PM
  • David,

     I can lock (disable) SA login with no impact on the SQL server and having enabled Login with "sysadmin" role will

    be appropriate replacement. (Aware about SQL authentication vs "Trusted" (AD) authentication.

    Another related questions:

    1.Just read a blog about changing SA password. There are suggestions restarting all SQL services after changing SA password.

    One person saying that it is unnecessary.

    After your answer in previous post I feel that it is unnecessary. What would you say?

    2. Also, few people mentioned that after changing password in SMSS they cannot login (login error).

    Is it anomaly? or there is a chance for such behavior?

    Thanks!

     


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    • Marked as answer by pob579 Thursday, February 16, 2017 3:51 PM
    Thursday, February 16, 2017 3:20 PM
  • Before you do disable or changing the sa account, ensure no one nor no applications being used has explicitly.

    once you got the information ,you can disable it-there is no issues,usually as per best practise it is good to disable the SA account.

    I dont think so ,it requires any restart of services.

    if incase the sa account renamed to different incase of <=sql2005 & planning to go for version upgrade to higher may lead issues but this is out of scope of your question. so.


    Regards, S_NO "_"

    • Marked as answer by pob579 Thursday, February 16, 2017 3:51 PM
    Thursday, February 16, 2017 3:40 PM
  • S_NO,

    > Before you do disable or changing the sa account, ensure no one nor no applications being used has explicitly.

    should I check that in DB Permissions node?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Thursday, February 16, 2017 3:51 PM