I recently had a domain account removed and later recreated. I got my old machine back but noticed I had lost access to SQL SERVER. I had originally installed SQL with my domain account, so that account was the only “sysadmin” in SQL. With that account having been deleted, I had lost control of SQL.

After some googling I found a solution that has given me control back. You have to run SQL in “single user mode” this option pretty much makes any “Local Admin” a “sysadmin” in SQL, than giving you the rights to add your new account as a sysadmin. Apparently previous versions of SQL would always allow “Local Admins” access, but that is not the case with the latest versions of SQL.  Since I am currently running BizTalk 2009, it means I am using SQL Server 2008.

For more information: http://msdn.microsoft.com/en-us/library/ms188236(v=SQL.105).aspx

You will first have to stop all instances of SQL Server, plus the SQL Server Agent. So I just went ahead and stopped all SQL Related Services.

1. Start | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager
2. Click on “SQL Server Services”
3. Right Click and Stop all your services

Open two Command Consoles – I ran both as Administrator

Window 1:

1. Change path to location of your SQL Installation
2. Typically: C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinn
3. Run the following command: sqlservr.exe –m

1. Command window will show log of SQL Starting and Running…

Window 2

• Switch over to your second Command Window
• Start the following Program: sqlcmd
• Type the following if creating a Windows Account:
  • CREATE LOGIN [DomainUser] FROM WINDOWS;
• Otherwise if using a SQL Account type the following:
  • CREATE LOGIN [LoginName] WITH PASSWORD = ‘SomePassword’;

  

• Type the following:
  • EXE sp_addsrvrolemember ‘LoginName’, ‘sysadmin’

  

After this I actually had to reboot before SQL would let me log in using my new Domain Account. Or as my screenshots show, my new “SQL Security” account.