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:
Logon-failed-for-login-due-to-trigger-execution

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):

Start-SQL-Server-Service-with-parameters-to-ignore-server-triggers

Server-is-in-single-user-mode-only-administrator-can-connect

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:

NET STOP MSSQLSERVER
NET START MSSQLSERVER /c /m /T3604 /f

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, 127.0.0.1 for local host).
This should work, enjoy! 🙂

Advertisements

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.”

    http://beyondrelational.com/modules/2/blogs/77/posts/11314/dedicated-administrator-connection-underappreciated-features-of-microsoft-sql-server.aspx

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s