A user in my domain, Colin Casio, has a SQL Server 2008R2 instance running with Mixed Mode Authentication.
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 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).
- Ensure the PSExec.exe is located on the SQL server
- 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
- When it completes a new command prompt window opens:
- You can check that this window is running in the context of user NT Authority\SYSTEM by running the whoami command:
- 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.
- 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):
- After GO has been entered the new login is created and can access the SQL Server via Management Studio.
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’;
Disclaimer: provided “AS IS” with no warranties and confer no rights