Wednesday, November 10, 2010

Restoring SQL Server 2005 Administrator Password

Never delete your Domain Admin account.  It will only make your life miserable when needing to access SQL 2005 and 2008 databases. :)

Here is how I was able to re-enable my new domain account (same name, different SID) on SQL 2005.

  1. Stop the SQL Server (OFFICESERVERS) service.
  2. Double-click on the service and copy the path to the executable
  3. Start the Command Prompt in Administrator mode
  4. Right-click to paste the executable and add -m to the end of the line to start the server in single user
    1. "c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" -sOFFICESERVERS -m
  5. Start another Command Prompt in Administrator mode
  6. Run SQLCMD.EXE (I wasn't able to connect to the OFFICESERVERS instance using any command line switches)
    1. At the first line type :connect servername\OFFICESERVERS
    2. If you don't succeed, look at the other command prompt to see if another service grabbed the only available session/ login.
    3. If something else grabbed your session, stop the sqlservr.exe with a CTRL-C and then Y
      1. Press up and restart the same sqlserver with the same -m switch
      2. Move faster on the second command line
  7. Once connected to the SQL instance you want do these steps:
    1. 1>create login [domainname\username] from windows;
    2. 2>exec sp_addsrvrolemember 'domainname\username', 'sysadmin';
    3. 3>go
    • Make sure that you put the domainname\username in square braces [] on line 1 and single quotes in line 2
    • Also note that the go command does not have a semi-colon
  8. Now stop the sqlserver from the command line with CTRL-C and answer Y to the question.
  9. Start the SQL Server (OFFICESERVERS) service like normal
The differences on SQL 2008 R2 is a little different but similar.  You need to start the sqlserver.exe with a trace flag 7806 so that it looks like this:
  • "c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER -t 7806 -m
Since this instance on SQL Server used the default instance of MSSQLSERVER, then you don't need to use the :connect command after starting SQLCMD.
  1. Start the Command Prompt in Administrator mode
    1. "c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER -t 7806 -m
  2. Run SQLCMD.EXE 
    1. If you don't succeed, look at the other command prompt to see if another service grabbed the only available session/ login.
    2. If something else grabbed your session, stop the sqlservr.exe with a CTRL-C and then Y
      1. Press up and restart the same sqlserver with the same -m switch
      2. Move faster on the second command line
  3. Once connected to the SQL instance you want do these steps:
    1. 1>create login [<<DOMAIN\USERNAME>>] from windows;
    2. 2>EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin';
    3. 3>GO
Two sites that helped me keep this all straight: