Here is how I was able to re-enable my new domain account (same name, different SID) on SQL 2005.
- Stop the SQL Server (OFFICESERVERS) service.
- Double-click on the service and copy the path to the executable
- Start the Command Prompt in Administrator mode
- Right-click to paste the executable and add -m to the end of the line to start the server in single user
- "c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" -sOFFICESERVERS -m
- Start another Command Prompt in Administrator mode
- Run SQLCMD.EXE (I wasn't able to connect to the OFFICESERVERS instance using any command line switches)
- At the first line type :connect servername\OFFICESERVERS
- If you don't succeed, look at the other command prompt to see if another service grabbed the only available session/ login.
- If something else grabbed your session, stop the sqlservr.exe with a CTRL-C and then Y
- Press up and restart the same sqlserver with the same -m switch
- Move faster on the second command line
- Once connected to the SQL instance you want do these steps:
- 1>create login [domainname\username] from windows;
- 2>exec sp_addsrvrolemember 'domainname\username', 'sysadmin';
- 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
- Now stop the sqlserver from the command line with CTRL-C and answer Y to the question.
- 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
- Start the Command Prompt in Administrator mode
- "c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER -t 7806 -m
- Run SQLCMD.EXE
- If you don't succeed, look at the other command prompt to see if another service grabbed the only available session/ login.
- If something else grabbed your session, stop the sqlservr.exe with a CTRL-C and then Y
- Press up and restart the same sqlserver with the same -m switch
- Move faster on the second command line
- Once connected to the SQL instance you want do these steps:
- 1>
create
login [<<DOMAIN\USERNAME>>]
from
windows;
- 2>
EXEC
sys.sp_addsrvrolemember @loginame = N
'<<DOMAIN\USERNAME>>'
, @rolename = N
'sysadmin'
;
- 3>GO
Two sites that helped me keep this all straight: