RESOLVED: Login failed for login [login name] due to trigger execution

It will happen to us all (at least once :))
Now you know how to fix it and not having to reinstall SQL Server!
A big thank you for my colleague Wim Kok who saved me!

So in my case, I detached an important database that was used to store logon statistics by using a server trigger.
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. If you’re lucky, you still have an open connection (as Administrator) and be able to attach the database again. Again, I my case I wasn’t that lucky 😦

The famous error message:

More info: Microsoft Support

Steps to take:
Step #1: Stop SQL Server and related services.
Step #2: Start SQL Server from the command prompt using the startup parameters: “–c -m –f“. Or via Services -> SQL Server Properties as shown below (doing this disables the login trigger):



Still having trouble?
“Server is in single user mode. Only administrator can connect at this time.”
Make sure that all services are stopped and create a batch-file (*.bat) that contains:


Execute the batch-file via Command (run as Administrator) -> Shift + Right-click

Now, start SSMS (run as Administrator) and try to connect (a suggestions would be to fill in an IP-address, for local host).
This should work, enjoy! 🙂

2 thoughts on “RESOLVED: Login failed for login [login name] due to trigger execution

  1. I don’t know, but let me know when you’ve tried it 🙂

    “A better alternate is to connect using DAC via the SQLCMD. To do so, the SQLCMD statement would look like:

    sqlcmd -S -E -A -d master

    The –A parameter indicates our intention to use the dedicated administrator connection.”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s