I got a call in the middle of the night about all the logins in SQL Server has been disabled. Logon failed due to trigger execution. The situation is quite scary, isn’t it? Well if you know what to do, then it is not except from the fact that you have to login in odd hours.
At first try to connect to the SQL Instance to find out the error:
The error message says “Logon failed for Login ‘sa’ due to trigger execution”. The second and third line of the error is little misleading.
It indicates that there must be a server level trigger someone has created due to which all the logins got disabled. The solution would be, find out the problem trigger and disable it. But you need to find out a way to get in to SQL Instance first.
You guessed it right! You need to use DAC (Dedicated Admin Connection) to connect in this situation. If you have not read the article where I have explained different ways to enable and connect using DAC click the following link.
Dedicated Administrator Connection (DAC)
Here I am going to show using command prompt. In the video link given at the end of the article, I have demonstrated using SSMS.
Open command prompt and use DAC to connect to the instance. In the below screen shot I am connecting to default instance using windows authentication and then run the below query:
SELECT name, create_date FROM MASTER.sys.server_triggers ORDER BY create_date DESC
As shown in the above example the trigger name is “TestLogin” and if you have observed I have used order by create_date desc, this is because after the latest trigger creation, nobody is able to login.
Now you can disable the trigger using the below query:
disable TRIGGER [TestLogin] ON ALL server
Once the trigger is disabled you will be able to connect to your SQL Instance. You can script out the trigger to check and rectify it.
If you can play sound, go ahead and watch the demo to fix the issue using SSMS:
I hope this helps. I would love to hear from you about any other similar issue or if you have any questions.
