Problem
Starting with SQL Server 2008, the local Administrators group is no longer added by default during SQL Server setup; you even have to use a manual step to add the current user as a local administrator. This means that it is possible, especially if you don’t use mixed authentication (or have forgotten the sa password), that you can be completely locked out of your own SQL Server instance. I’ve seen cases where an employee has moved on, but their Windows account, being the only one with Administrator privileges for SQL Server, had been completely obliterated from the system. Of course that person was the only one who knew the sa password as well, and being a local admin or even a domain admin might not help you.
The typical workaround I have seen employed is to restart SQL Server in single user mode. However, this approach requires at least some downtime; in some systems, this would be unacceptable. And depending on what needs to be managed on the server, it might not be feasible to wait for a scheduled maintenance window.
A more extreme workaround is to shut down SQL Server, copy all of the MDF/LDF files, install a new instance of SQL Server, and attach all of the user databases. In addition to downtime, the problem here is that you must also re-create all of the logins, linked servers, jobs, and other elements outside of the database in order to have a fully functioning system again. Plus, unless you uninstall the old instance first, the new instance will have a new instance name, and therefore all client applications will need to be updated.
Solution
Thanks to Mark Russinovich of Sysinternals fame, there is a very painless way to solve this problem without any downtime: PsExec. While it wasn’t one of its primary design goals, PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account, which – unlike “regular” Administrator accounts – has inherent access to SQL Server.
The process to get back up and running is quite simple. Log in to the server where SQL Server is running, as an account with local Administrator privileges. Download and extract PsExec.exe. Start an elevated command prompt (Shift + Right-click, “Run as Administrator”). Run the following command, adjusting for your actual path to Management Studio, which may be different:
This command tells PsExec to run SSMS interactively (-i) and as the system account (-s).
You will get an error message if you are not an Administrator. You will need to agree to the license terms in order to proceed. When Management Studio launches, it will prompt you to connect to a server. You will notice that the authentication mode is Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM:
Once you connect, in Object Explorer, you will see that you are connected to the server as NT AUTHORITY\SYSTEM:
Now, you can go in to Security > Logins and add your account as a sysadmin, add other admin accounts, update the sa password, and do anything else you need to do to make your instance manageable. As you can see, I was able to connect in this way to both SQL Server 2008 and SQL Server 2012 instances from an instance of Management Studio 2012. I also confirmed that this process works when connecting to a SQL Server 2008 instance using the 2008 version of SSMS. In both cases, I was logged in to Windows as a local administrator, but the account had no explicit access to either SQL Server instance.
You can connect to additional instances on the local server using the Connect > Database Engine dropdown in Object Explorer, or by right-clicking a query window and choosing Connection > Change Connection.
Caveats
- You must be a local Administrator to masquerade as NT AUTHORITY\SYSTEM.
- You may need to disable UAC.
- You may also be able to do this remotely, but since I work primarily in virtual machines, I did not test this.
- If you change the sa password, and you’ve used the sa account for external programs (which you shouldn’t do), you will need to update those programs. Here is a tip for updating the password used in maintenance plans.
- Leave the command prompt running in the background until you’re done with all of your changes – if you inadvertently Ctrl+C from within the command prompt, SSMS will vanish.
Conclusion
It is quite common to get locked out of a SQL Server instance, and having been there, I know it is quite frustrating. PsExec can get you into your systems without a lot of the headache that can be caused by more brute force methods. You should download PsExec and keep it handy; it can prevent you from going to extreme measures, and having it already available can help in cases where the SQL Server machine you’re trying to recover does not have ready access to the web.
Next Steps
- Download PsExec and make sure it is readily available to all of your SQL Server instances.
- Test the method above and confirm that you are able to use the tool to elevate privileges and gain access to SQL Server even if your Windows account has no such privileges.
- Review the following tips and other resources:
