SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem

Recently I have received a mail from one of blog reader, who explained his problem as following:

“I have tried scrip to create logon trigger from your blog post “Restrict Login from Valid Machine IPs Only (Using Logon Trigger)” BUT problem is that, I forgot to put localhost in my safe list, and now I am unable to login to my instance.”

Well, if same happened to you, then you need to login using Dedicated Administrator Connection. What is DAC and how to you use it Read This.
DAC can be established using sqlcmd or through SSMS. On command prompt, type this to establish connection.

Sqlcmd –S localhost –d master –A

You can provide instance name instead of localhost. Next thing is to disable our logon trigger, using following command.

DISABLE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

Where “tr_LogOn_CheckIP” is the name of our logon trigger. On next line type GO to execute DISABLE command.

Now you can login to your database server. Once login, check out trigger is disabled.

You can achieve all this through SQL Server Management Studio. To establishing dedicated connection, click on  FILE----NEW----Database Engine Query

Login through valid SYSADMIN user, by providing server name with extra word and a colon, i.e. Admin:

In query window, type same tsql and execute to disable trigger.

And never forget to add your server IP or <localhost> in safe list, while creating logon trigger.