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

Recently, I received a mail from one of the blog readers, who explained his problem as follows:

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

Sqlcmd –S localhost –d master –A

Well, if the 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.

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

DISABLE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

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

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

You can achieve all this through SQL Server Management Studio. To establish a 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.