Friday, September 18, 2015

Lost SQL Server access, forgot 'sa' password no clue how to gain access?



I recently encountered an issue where I didn't have access to SQL Server and I was asked for help to debug and gain back control on the system and the server hosting sql server database. 

Here is what to do.

Step1) Login to the server as administrator. 

Step2) Download PSExec tool from 

https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Its windows sysinternals utility. 

Why? 
PsExec allows you to run programs as the NT AUTHORITY\SYSTEM account


Step3) Find the location of your SSMS.( SQL Server Management Studio) 

Right click on the properties of SSMS and click on shortcut tab. 
Copy the location to a notepad. 


Step4) Open Command Prompt run as administrator

Step5) Navigate to the folder where you have downloaded and extracted PSexec tool.

Step6) Run the following command to start your SSMS.

PsExec -s -i "F:\SQLServer\Installs\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"

Please refer to https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

 s-    Runsthe remote process in the System account. 
i- Runs the program so that it interacts with the desktop of the specified session on the remote system. If no session is specified the process runs in the console session.



Step7 )  go to Security and gain access or add user as login and add them to the sysadmin server role.