How to easily access SQL Server 2019 if locked out

How to add path to environment variable in windows

Problem

From time to time as DBAs, we might lose access to our database instance, whether it be forgetting our password after a long vacation or if you’re a new DBA in an environment and the outgoing DBA failed to provide credentials to the database. This tutorial will show you how to easily access SQL Server 2019 if locked out. This post might be titled How to easily access SQL Server 2019 if locked out, but this method works very well with previous versions of SQL Server.  In this tutorial, we will be using the PsExec tool.

What is PsExec?

According to Microsoft’s website, PsExec is a light-weight telnet-replacement that lets you execute processes on other systems, complete with full interactivity for console applications, without having to manually install client software. PsExec’s most powerful uses include launching interactive command-prompts on remote systems and remote-enabling tools like IpConfig that otherwise do not have the ability to show information about remote systems.

How to easily access SQL Server 2019 if locked out

  1. To begin, we want to head over to Microsoft’s website and download PsExec.
  2. Once downloaded, extract and copy the folder to a central location (for me, it’s the C drive root. C:\PSTools)
  3. Copy the path to the executables and paste it in your environment variable path and click ok. Please see the gif below on how to do this.How to add path to environment variable in windows
  4. Locate and copy the path to the Ssms.exe file for your management studio. Mine is located in “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE” yours might be different.
  5. Run your command prompt as administrator by right-click, Run as Administrator
  6. Paste the following command in the command prompt and press enter:  PsExec -s -i "Path to management studio .exe file". In my case i would run the command: PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"
  7. Management studio should open. Ensure that windows Authentication is selected and NT AUTHORITY\SYSTEM should be in the User Name Section. Hit Connect. Connecting to sql server with NT AUTHORITY\SYSTEM Account
  8. You can now proceed with changing the database SA password.

 

Conclusion

This method gives a DBA a quick and easy way to reset the database sa password. for other methods on how to easily access SQL Server 2019 if locked out check out: Accessing Sql Server 2019 when logins disappear after installation.