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:

How to access a newly installed SQL Server 2008 R2 instance if you do not know the login and password

How to add a Windows user to the sysadmin fixed server role in SQL Server 2005 as a failure recovery mechanism