locked
SA password questions RRS feed

  • Question

  • Hello,

    I have 2 questions that are related to the SA password:

    1) I have adopted the role as a SQL DBA at my current position. I have been a SQL developer for 10 years so the DBA role is a little fuzzy to me. We had a consultant come in that set up one of our SQL Servers and neglected to give us the SA password or we dropped the ball and did not document it. Not only that, but he did not create any other users with sysadmin rights so I cannot simply reset the password using different login. Does anyone know how I can reset this password?

    2) I have set up a new SQL Server that I am now responsible for. What are your recommendations on the SA Password? Should it be disabled and should I just create logins that are a part of the sysadmin role? I have read that hackers look for the SA login first, so I do not want to take any chances on my first DBA role.

    Thank you all in advance for your time :)


    Dave SQL Developer

    Tuesday, February 5, 2013 10:17 PM

Answers

  • Using Google, I found this marvellous blog post: http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

    Once you have the SA password, you should review who is the sysadmin role (so that the consultant or someone else is against your expectation.) Then add your own Windows account to sysadmin. If there are more persons who should have access, it may be better to create an AD group for DBAs, and then add that group to sysadmin.

    Once you have set up yourself as sysadmin, disable SA. And disable SQL authentication, unless you know that you need it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by DaveDB Tuesday, February 5, 2013 10:53 PM
    Tuesday, February 5, 2013 10:36 PM

All replies

  • Hi,

    What version of SQL Server do you have installed?  It used to be that members of the servers local administrator group were SQL Server admins by way of the BUILTIN/Administrators login.  Depending on the version, you may be able to add a local server admin, then you'd have SQL Server sysadmin access too.  Then you could easily reset the SA password.

    Reserve sysadmin privileges for only special cases.  In most cases, server roles and database roles/permissions will be enough for the typical user. 



    Thanks, Andrew

    Tuesday, February 5, 2013 10:27 PM
  • Using Google, I found this marvellous blog post: http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/

    Once you have the SA password, you should review who is the sysadmin role (so that the consultant or someone else is against your expectation.) Then add your own Windows account to sysadmin. If there are more persons who should have access, it may be better to create an AD group for DBAs, and then add that group to sysadmin.

    Once you have set up yourself as sysadmin, disable SA. And disable SQL authentication, unless you know that you need it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by DaveDB Tuesday, February 5, 2013 10:53 PM
    Tuesday, February 5, 2013 10:36 PM
  • Hi Andrew,

    I am using SQL 2008 R2.

    How would I access the BUILTIN/Administrators login? The only member of the admin group is SA, so I cannot create any logins.  Unfortunately I was not with the company when this box was set up or I would have ensured that my windows account had admin rights.

    Thanks


    Dave SQL Developer

    Tuesday, February 5, 2013 10:45 PM
  • Hi Erland,

    I am unable to stop the service as I have users connecting to this DB. I do agree that this is a great post, but perhaps my question should be.... How can I change the SA password with restarting the SQL Service?

    Thanks


    Dave SQL Developer

    Tuesday, February 5, 2013 10:48 PM
  • In that case, Erland's link should hopefully do the trick.  Here's another link to pretty much the same info: http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx



    Thanks, Andrew

    Tuesday, February 5, 2013 10:50 PM
  • It doesn't look like I can do this without stopping the SQL Service. Thank you guys for your help!

    Dave SQL Developer

    Tuesday, February 5, 2013 10:52 PM
  • Yes, you can't do this without restarting SQL.  This is quite intentional.  A Windows Administrator can always take ownership of a SQL Instance, but only by doing things only a Windows Administrator can do.  You can put a lock on the door, but an Administrator can take the hinges off.

    Here's the process in a handy batch file:

    http://blogs.msdn.com/b/dbrowne/archive/2010/06/11/batch-file-to-local-administrators-a-sysadmin-login-in-sql-server.aspx

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, February 5, 2013 11:14 PM