Locked out of a SQL instance? Two methods to get back in without restarting the service

Jon Seigel

Hopefully you don’t lock yourself out of your servers frequently. It does happen from time to time, though, and it’s handy to know how to get out of the jam quickly.

The following methods will allow you to fix things while the SQL instance stays online and operational. Note that they do rely on there being an active login for NT AUTHORITY\SYSTEM (the local system account) in the SQL instance. This login is created by default, but it can be removed or disabled. (Edit: on 2008 R2, the system account login is sysadmin level, while on 2012 it has been restricted.)

If you don’t have a local system account login in the SQL instance (or another suitable login you can use or impersonate), you will have to resort to the method that involves restarting the instance in single-user mode, and use either SQLCMD or a query window in Management Studio to fix things up.

Warning: be extremely careful when using either of these methods, as you will have the ability to run commands as the local system account.

The Built-In, Slightly Complicated Way

Note: service Interactive Mode is deprecated; I tested that it works on Windows Server 2008 R2 SP1. It also works on Windows 7 if you have instances installed on your workstation. (Edit: This method does not work on Windows Server 2012 RC. Use the other method.)

  1. Open a command prompt on the SQL server. We’re going to create a Windows service that launches a command prompt instance running as the local system account. Do this by typing the following command (no outer quotes, the spaces are important) and pressing enter: “sc create RecoverSqlPassword binPath= “cmd.exe /k start” type= own type= interactive”
  2. Assuming you got a success message in the previous step, start the service by typing the following command and pressing enter: “sc start RecoverSqlPassword”
  3. While the command in the previous step will say it failed (this is okay), in the task bar, you will see a new window called Interactive Services Detection trying to get your attention. Bring it into focus and click the View the Message button. This will switch you to a different desktop with a command prompt window and another Services dialog that allows you to return to your own desktop. (While you can switch back and forth between the desktops as much as you want, unfortunately you can’t copy/paste between them.)
  4. In the command prompt window, either use SQLCMD or browse to and launch Management Studio to fix the login issue. When you’re done, close/exit the command prompt and click the “Return now” button.
  5. Clean up by deleting the service that was created in Step 1 by typing the following command and pressing enter: “sc delete RecoverSqlPassword”

The Not-Quite-Built-In, Easier Way

  1. Download the SysInternals tool PsExec and unzip the .exe file to a suitable location on the SQL server.
  2. Open a command prompt and change directory to the aforementioned location.
  3. Run the following command to start a command prompt process as the local system account: “psexec -s cmd.exe” (note: unlike the other method, this starts the process within the same command window, and without a desktop switch).
  4. Either use SQLCMD or browse to and launch Management Studio to fix the login issue. When you’re done, either use the “exit” command to return to the original command prompt context, or just close the command prompt window entirely.