Click here to Skip to main content
Click here to Skip to main content
Technical Blog

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

, 18 Sep 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Using logon trigger, users from valid IP addresses can be restricted, but what if you forgot to add in safe list. You will be unable to login though you have sysadmin rights. Use DAC to disable logon trigger.
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.”

Sqlcmd –S localhost –d master –A
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.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

aasim abdullah
Team Leader CureMD
Pakistan Pakistan
Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite pencils.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 18 Sep 2012
Article Copyright 2012 by aasim abdullah
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid