How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist

An actual backdoor into SQL Server does exist that does not require restarting and/or rebooting anything into single-user mode. I have done this on systems where I did not have access but needed to check stuff.

Download PSexec tools from here. Place this on the server and then in a command prompt execute this command:psexec -i -s SSMS.exe, or sqlwb.exe

This will open up SSMS as the system account that has sysadmin access to the instance of SQL Server. This is done during installation of SQL Server, however I have heard that this will not be so with SQL 2012.

SQL Server 2008 is different than SQL Server 2005, in that local administrators no longer inherit the sysadmin fixed server role.

When SQL Server 2008 is installed, it prompts you to designate an account to be added to the sysadmin role. If you receive the box after the software is installed, however, this doesn't help you very much.

Fortunately, Microsoft preserved the 2005 functionality when SQL Server is running in single user mode. Here's what you do:

• log into the server as windows local admin
• stop sql server
• at the command prompt, in the directory where sqlservr.exe resides, type sqlservr.exe -m, and press enter - this will start SQL Server in single-user mode
• open up SQL Server EM, and add your account to sysadmin fixed server role
• stop sql server, restart (at command line or in SQL Server EM

Even the most uber-admin type of connection option Dedicated Administrator Connection (DAC), which can only be used as a local connection, and lets you undo all kinds of evil, still requires login credentials. So I don't think there's an official way to do this.

The quickest way to resurrect this system may be to shut down SQL Server, copy the user database files somewhere safe, uninstall, reinstall (making sure to service pack up to at least the level you were previously at), copy the files back and attach the databases. (Not sure the copying out/back is required, but just to be safe...).

You'll still need to manually recover server level objects (e.g. logins)