Regain Admin Access to SQL Server

The Scenario

A user in my domain, Colin Casio, has a SQL Server 2008R2 instance running with Mixed Mode Authentication.

The Problem

Somehow Colin’s Windows account login got deleted by a user logged in as the sa account. Additionally that person has since left the company and no one knows what the sa account password is. Neither Colin nor anyone else can now login to the SQL server via Management Studio. We could uninstall SQL and reinstall and recover all the databases from backup it but this is going to take a lot of time and effort.

The Solution

The quicker solutions uses the NT Authority\SYSTEM account, which in the versions of SQL Server listed below has sysadmin rights by default. By switching context and running SQL commands as this account you can add new logins and permissions to the SQL server even when you yourself have no permissions or security login. You have to know the SQL name and instance you want to grant access to.

NB This solution has been tested on SQL Server 2008, 2008 R2 and 2012 (I’m not saying it won’t work in 2014, I just haven’t tested it). It also requires the Microsoft SysInternals tool PSExec (download here).

  1. Ensure the PSExec.exe is located on the SQL server
  2. Logged on to the SQL server open an elevate command prompt and browse to the location of PSExec.exe and type PsExec.exe -s -i cmd
  3. sql_admin1
  4. When it completes a new command prompt window opens:
  5. You can check that this window is running in the context of user NT Authority\SYSTEM by running the whoami command:
  6. whoami
  7. Type SQLCMD -S SQL_Server_Hostname/InstanceName to enter the SQL command prompt. NB If your SQL server just uses the default MSSQLSERVER instance change the command to: SQLCMD -S SQL_Server_Hostname.  The title bar changes to SQLCMD and the prompt changes to 1> if successful.
  8. In my example I need to create Colin a login and give him SQL Admin rights (not best practise but will suffice for this scenario). Enter the following three lines of text, pressing enter after each one (see below for text you can copy/edit/paste):
  9. sql_admin2
  10. After GO has been entered the new login is created and can access the SQL Server via Management Studio.

Example:

SQLCMD -S SQL_Server_Name (or if using an instance name other than MSSQLSERVER type SQLCMD -S SQL_Server_Name\Instance)
1>CREATE LOGIN [domain\username] FROM WINDOWS;
2>EXEC sp_addsrvrolemember ‘domain\username’, ‘sysadmin’;
3>GO

 

Disclaimer: provided “AS IS” with no warranties and confer no rights

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s