Steps to recover access/reset SA Password to SQL Server Instance without downtime

Problem: There may be situation where you are unable to access the SQL Server. In this article I will guide you how you regain the access to SQL Server instance.
Below are the possible reasons why you wouldn’t be able to access the SQL Server instance
- Built in Administrator is removed
- Built in Administrator is not available
- Forgot SA password
- Forgot add the DBA account on the SQ Server instance

Most of the time to resolve this kind of issue we are restarting the SQL Server in single user mode, which requires down time, here we will follow the approach where we don’t need any downtime to get the access to SQL Server instance.

Solution:
You can follow below steps to get the access to SQL Server instance without downtime.
Step 1: Make sure you have administrator level access on windows
Step 2: Download the PSExec, PSExec utility allows you to connect the SQL Server instance under the context of NT AUTHORTY\system account, which has default sysAdmin access on SQL Server.
Step 3: Open the command prompt as administrator and type the below command, it will open SSMS and under NT AUTHORITY\SYSTEM account context and you can change password or add account etc.
1
   
PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

Step 4: Open new query window from SSMS and either you can change SA account password or add new account.
1
2
3
4
   
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'password'
GO