Recovering System Administrator Privileges

Recently I ran into an issue where I had a SQL Server instance that needed to have a database restored on it.  A pretty simple task, right?  Well, it turns out that I did not have rights on the system to do the restore.  My client did not have a system administrator password and did not have a user which we could use to grant us the necessary access.  But, I did have local system administrator privileges on the server.  As long as we have local admin rights on the server we can get the necessary privileges from SQL Server.

1. The first step to recovery is having the ability to bring the instance down.  We can do this a few different ways: either through Enterprise Manager (SQL Server Management Studio), services, or through Configuration Manager.  Once the instance is brought down we need to bring it up in single user mode using the ‘m’ flag.  I usually do this through the command prompt using ‘net start’ or you could put the trace flag in the startup parameter files; however you feel most comfortable.  Example: Bringing the default instance down and starting it back up in single user mode.



2. Use SQLCMD (2005-2008) or iSQL (2000) to connect to the instance.  Make sure that Object Explorer and applications are not connected to the instance.  You can also do this through SSMS or Query Analyzer. Example: Connect to the instance once in single user mode.



3. Add yourself as a user and grant yourself administrator privileges. Example: Adding a test user and giving it system administrator privileges.



4. Congratulations!  You have successfully given yourself administrator privileges, so now stop SQL Server and start it back up without the single user flag.

Tim Foley, Sr. SQL Server DBA