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.
- Start | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager
- Click on “SQL Server Services”
- Right Click and Stop all your services
Open two Command Consoles – I ran both as Administrator
Window 1:
- Change path to location of your SQL Installation
- Typically: C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinn
- Run the following command: sqlservr.exe –m
- 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.