Temporarily Changing an Unknown Password of the sa Account
This was found on: http://www.simple-talk.com/sql/t-sql-programming/temporarily-changing-an-unknown-password-of-the-sa-account-/ and I archived the content below, so I couldn't lose it, and their site wouldn't go away...
GOOD STUFF!!!
Temporarily Changing an Unknown Password of the sa Account
10 July 2007
I have been working with SQL Server since version 6.5. In the early days, SQL Authentication was a given and I understood most of its nuances. Eventually, SQL Server 7.0 and 2000 surfaced along with Active Directory and the promise of Windows Authentication, which was a godsend. Very soon, SQL Authentication began to feel like a relic, a fossil left over for the SQL archeologists clinging to history, decrying backward compatibility as essential to forward momentum. So, I started to install every instance of SQL Server in Mixed Mode Authentication, never really wading from the shallow end to the 6 foot depths of SQL security potential.
I'm probably like many other DBAs in this respect. And, likewise, the following scenario is likely to visit most DBAs at some point in the travels and travails of their career: an IT manager stands at your cubicle waiting for you to acknowledge his presence. You turn slowly away from a half-baked stored procedure or from setting a background image on your desktop, and see the concern on his face.
"Do you know the 'sa' password for Server_57? We are going to need it for an upgrade to our third-party Help Desk management software."
The logical flow of the conversation usually goes something like this:
- You are the DBA. You should know the sa password
- I use Windows authentication on 97% of the SQL Servers I manage
- I manage 60 SQL Servers
- All SQL Servers are in Mixed mode authentication
- I have installed 20 of the 60 servers
- I know the sa password on all of the 20 servers I installed
- Other DBAs, no longer with the company installed the other 40 servers
- The other DBAs did not fully document the sa password on all instances.
- I know the sa password on 30 of the remaining 40 servers
- Server_57 is not in the 30 remaining servers I know the sa password to
- No one remaining in IT knows the sa password on Server_57
- Server_57 is a production SQL Server
- Server_57 has jobs that may fail if the sa password is changed to a known value to perform the upgrade request
- The upgrade that is requested is high priority and must be accomplished ASAP
So, no, you do not know the 'sa' password, but…you do know that the 'sa' password will only be needed for the upgrade, not for ongoing connectivity, so you proffer a solution, "I will change the 'sa' password for you for long enough to complete the upgrade, and then I will need to change it back again. This is because I do not know what processes may be trying to use 'sa'".
It is permissible, at this point, to wince at the dreadful thought that there might be a process tied to the 'sa' account. It might seem preferable to change the 'sa' account password permanently. Thoughtful DBAs would, if they did this, inform job owners of the change ahead of time. Thoughtful job owners should, by right, not be using 'sa' to begin with.
The sp_help_revlogin stored procedure
How does one go about changing a password back to what it was if one does not know what it was initially? It is possible to do this in both SQL Server 2000 and SQL Server 2005. The steps are quite different, but both rely on a very handy stored procedure provided by Microsoft. The stored procedure, sp_help_revlogin, was designed to migrate logins from one instance of SQL Server to another, in order to prevent orphaned uses in restored databases. I will not elaborate on the orphaned user issue here, but I will state that it relates to the Security ID or SID created with each user that is specific to an SQL Server instance. What is important is that sp_help_revlogin can be used to generate a current hashed password for each SQL Server authenticated login, which can then be used for reverting to the original password should something goes awry after changing to a known password.
Using sp_help_revlogin on SQL Server 2000
Executing sp_help_revlogin on a source SQL Server produces a script that, when run on a target SQL Server, cre