With LocalAdmin become sysadmin SQL Server

Question

I am a local Admin on my machine. My account wasn't added when SQL Server was installed, and I don't want to have to reinstall SQL Server. It worked until I tried to log and run the commands via SQLCMD or SSMS. I got the error message

Login failed for user <Machine_Name>/<Windows Name>. Reason: Server is in single user mode. Only one administrator can connect at this time...

I found this blog and added the application name after the m switch. But no dice, I still get the above error. The User Account looks fine I guess, I am an Administrator.

Also, I had to start the cmd prompt as an Admin to start sqlserver in single user mode, or else I couldn't have access to the log files that the instance was using and it was giving me an error to that. So I ran the cmd prompt as admin. But still, I am the same admin trying to run SQLCMD or SSMS. It shouldn't be telling me there is already an admin logged in, because I am that admin. What am I missing?

This is on SQLExpress.

Answer 1

For such a situation we have made use of PSExec utility to get regain access on the SQL server.

You can download the utility from here , extract and copy the exe file to the drive location from where you can run below command.

Once downloaded you need to open the cmd with admin and run below:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

The path above may vary, like for one of our SQL 2k8 , we found the path to be:

"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

This will make you connect to the current via sever Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM.

YOu can create the required login from here:

Answer 2

I did something similar when first using DAC (Dedicated Admin Connection). In my case it turns out that I used SSMS to grab the one and only DAC, I did not realize it for sometime.

For example if you grabbed the connection with SSMS, you can close and re-open SSMS to release the connection.

As a last resort rebooting the server, will release whatever has the DAC connection.

See also "When you start an instance of SQL Server in single-user mode, first stop the SQL Server Agent service. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user."