I had a rather anxious phone call from a DBA friend today
He had a production SQL 2008 server running in Window Authentication mode, someone had removed the main admin login.
He was getting quite upset as he could not see any possible way to add it back in again, this account was the only account with admin privileges.
I knew that local system had admin rights on the server so decided to try and use this as my way in, this did not go as smoothly as planned.....
The server OS was 2008 R2
Using the AT command to run a command prompt interactively was not allowed due to the OS (at time /interactive cmd.exe) and psexec -i -s cmd.exe spawned command prompts that were not interactive, so after a little think I came up with the following solution.
I wanted to create a service that would allow me to spawn a command prompt under the context of the local system
sc create syscommand binpath= "cmd /K start" type= own type= interact
Then I started the service
sc start syscommand
Voila, apart from a few minor errors, the command prompt popped up (sigh of relief)
I then ran the SQL Server Management Studio (ssms.exe)
Added in the admin account again, set the correct level of access
removed the service account
sc delete syscommand
I left it to the SQL DBA to find who made the change and deal with them appropriately.... (I still think he did it himself, I will ask him over a beer next time I am in the UK)
Wasn't me :-)
A very neat workaround there David thanks for sharing!