only one administrator can connect at this time

Any thoughts on how I can limit the access during this maintenance? I've even tried to remove both named pipes and TCP/IP from the accepted connection types but that didn't seem to make a difference. Of course, 2000 doesn't have SQLCMD but has OSQL. Here is what I had done during the installation of SP2.

I was able to quickly respond to this question because I had faced this situation when I had installed SQL Server 2008 SP2 on my VM. I know with newer SQL versions one can limit the single user mode to only accept SQLCMD. Only administrator can connect at this time. I have the SQL agent shut down, I am not running enterprise manager and, as far as I can tell, there are no other admins connecting to the system. Only one administrator can connect at this time. It keeps saying 'Login failed for user ''. I understand if I can get the database into single user mode and log in I can add a new local account to the database with the sysadmin role, however I can't seem to get logged into the single user mode. Unfortunately, I cannot reinstall the instance and I only have short windows of opportunity for downtime. Now, start SSMS (run as Administrator) and try to connect (a suggestions would be to fill in an IP-address, 127.0.0.1 for local host).Įnter your email address to follow this blog and receive notifications of new posts by email.I had a faulty script that unfortunately removed all members from the SysAdmin role. Make sure that all services are stopped and create a batch-file (*.bat) that contains:Įxecute the batch-file via Command (run as Administrator) -> Shift + Right-click Only administrator can connect at this time.” Or via Services -> SQL Server Properties as shown below (doing this disables the login trigger):

Step #2: Start SQL Server from the command prompt using the startup parameters: “ –c -m –f“. Step #1: Stop SQL Server and related services. If you’re lucky, you still have an open connection (as Administrator) and be able to attach the database again. The thing is that detaching it was easy, but it kicked me out of SQL Server and (ofcourse) it didn’t let me re-connect to the SQL Server Instance. So in my case, I detached an important database that was used to store logon statistics by using a server trigger. Now you know how to fix it and not having to reinstall SQL Server!Ī big thank you for my colleague Wim Kok who saved me! It will happen to us all (at least once :))