Skip to content

Disabling DDL Triggers

August 21, 2012

Suppose you want to stop using a DDL trigger for a short period of time, such as the login trigger I created recently. If you want to disable an index, you use

ALTER INDEX xxx DISABLE

That doesn’t work for triggers. The ALTER TRIGGER syntax is used for changing code.

You could use ALTER TABLE on DML triggers, but not for DDL triggers. The DISABLE TRIGGER DDL can be used.

To stop tracking user logins, I can use:

DISABLE TRIGGER CatchLogins ON ALL Server
;

There is an ENABLE TRIGGER syntax as well to turn the triggers back on. These two commands allow you to save the trigger code, but have it enabled or disabled as needed.

About these ads

From → Blog

5 Comments
  1. m neale permalink

    Thanks Steve, can I press this just a little further. In SQL 2008 R2 and in 2012 is there an event raised when a trigger on a specific table is enabled or disabled? For security purposed I have been looking to see if I can capture the current user whenever a deny update or deny delete trigger is disabled. The data in the table is very sensitive and there needs to be an absolute chain-of-custody for that data.

    I already have a C# application that will run on a tight schedule using the SMO to enumerate and capture the metadata for tables, sprocs and triggers. But for the trigger I get only the datetime of the last update. That works fine up to a point, but if this were to go into a court of law I could not absolutely say who did what with the deny update trigger and who might have manipulated data. The only thing I can say is that the trigger was disabled some time during the two times the c# application ran.

    I have been looking for an event and the only thing I can find appears to say that Microsoft did not feel that an event was needed. Do you know of anything?

    Thanks!

  2. Off the top of my head, couldn’t you use a DDL trigger to capture the ALTER or DROP TRIGGER events? They should work with DDL triggers, and you could log this data to a table that you’ve removed access from for all users. You could even have this log to the EVENT LOGS as well and lock down access there.

    Ultimately in a court of law you can point to an account that made changes, but you can’t prove that a person made those changes. They could potentially blame someone else, saying they shared their name/pwd, so that would be an issue.

    • m neale permalink

      I agree that you should be able to catch the alter and the drop. Create is also easy. However the trigger can be enabled and disabled as well. Those are the two actions that are of interest.

      And correct intimately in a court of law you can only point to the account. And in this case the only account that is possible is the DBA and assigned backup. If any other hits the data we would like to know that. And yes if credentials are shared you could potentially blame someone else. However, if the agreement with the DBA is that credentials are shared only in the select group and another person actually did the update the issue would be the violation of procedure by the person who shared the credentials.

      That all aside, what is really needed is to identify that any enabled or disabled triggers were acted upon only by users with our credentials. If that credential is hacked there are other protections in place that will assist in proving the data.

    • What you might think about is a trace setup to just capture trigger changes and write the trace data to a file with ONLY write (insert) rights for the SQL Server service account. Add READ rights for your auditing people, who should not have admin rights on the SQL Server.

    • m neale permalink

      that is an excellent suggestion. I’ll look into that.

      Thanks for the post/blog and for your responses.

      M.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,956 other followers

%d bloggers like this: