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.
Best Answer
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:
The path above may vary, like for one of our SQL 2k8 , we found the path to be:
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: