Wednesday, August 21, 2013

Login failed for user Reason: Server is in single user mode. Only one administrator can connect at this time.


I forgot my "sa" password and lost administrator account so I have started sql server using single user mode as below

1. Open sql server configuration manager
2. Added ;-m as a last parameter in startup parameters in advanced tab
3. Restarted the service
4. Start - run - cmd -
5. SQLCMD -SServerName

Then I got the below error message
Login failed for user Reason: Server is in single user mode. Only one administrator can connect at this time.

To fix the issue I have modified -m to -mSQLCMD in step 2 and it got fixed

After login into SQLCMD then issue the below commands to create new login

create login kalyansa with password='password1'
sp_addsrvrolemember 'kalyansa','sysadmin

or

create login [domain\kalyan] from windows;
sp_addsrvrolemember [domain\kalyan],'sysadmin'

You need to remember below points
1. Stop the SQL Server Agent
2. Make sure no other account is logged into SQL Server

 

2 comments:

  1. Excellent.. worked for me as well.. thanks a lot..

    ReplyDelete
  2. Thanks Kalyan, this has helped me.

    ReplyDelete