I have started my SQL Server 2008
sqlcst02\inst01
in Single User Mode
by appending the ;-m
to the end of the startup parameters for this instance in SQL Server Configuration Manager
. I have stopped SQL Server Agent
for the inst01
instance. My account is a member of the Domain Admin group
and I have attempted to login using sqlcmd -S sqlcst02\inst01
and keep getting an error that the service is in Single User Mode
and only one administrator can be logged in. I tried adding ;-m"sqlcmd"
to the startup parameters to limit the connection to just sqlcmd
but I get the same results. I have tried this logged in as my own account and logged in as the local administrator account. I have started the command prompt as myself and as administrator but I get the same results. I have tried connecting using ADMIN:sqlcst02\inst01
in SSMS
. No luck.
I'm attempting to reset the sa
password as none of this information was left for me by the previous admin. This is maddening. I've done this before on other machines with no issues.
Best Answer
Another alternative to recovering access to a SQL Server is outlined in this article, and doesn't require restarting in single user mode:
http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/
Essentially, download PSExec, then run it with the following command line (fill in your location of SSMS.exe):
You will be prompted to connect - the authentication mode will be Windows auth and hard-coded to
NT AUTHORITY\SYSTEM
. Once connected, you will be able to go into security and fix the sa password, add yourself as admin, add other admins, etc.The article above has several caveats you should read before trying this, and you may also want to review the comments at the bottom.