1

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.

2 Answers 2

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:

0

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."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.