Below are the steps you need to perform to grant SYSADMIN access to a user in SQL Server in case you are completely locked out.
1. Download PSexec to connect using SQL Server Management Studio using the NT Authority\System
Download PsTools from https://download.sysinternals.com/files/PSTools.zip
Unzip the content and copy PsExec.exe to C:\Windows\System32
2. Stop the SQL Server and SQL Server Agent services on the server.
3. Open a cmd prompt window as administrator and navigate to SQL Server’s Binn directory. You may need to adjust your path based on your install location.
ex. C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Binn
4. Once you are in SQL Server’s Binn directory run the ‘sqlservr -m’ command to start SQL Server in single user mode as shown below. Had to add the location of the ERRORLOG
sqlservr -m -e C:\Program Files\Microsoft SQL Server\MSSQL11\MSSQL\Log\ERRORLOG;
If it’s a named instance:
sqlservr -m -s <instancename> -e C:\Program Files\Microsoft SQL Server\MSSQL11.SERVICECORE\MSSQL\Log\ERRORLOG;
After the SQL Server instance was started in single user mode, I was receiving logon errors form other users that were trying to logon from the application, when it was restarted in single user mode. I ignored the errors and moved on.
5. Execute PsExec – may need to adjust the ” marks and file locations
run cmd as administrator
PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”
The above command will launch SQL Server Management Studio and gives you a “Connect to Server” window and the User Name will be pre-populated with NT AUTHORITY\SYSTEM
6. Click Connect and then go in to Security > Logins and add your account as a sysadmin
7. restart the sql server services