Audit for DISABLE/ENABLE Triggers IN SQL

Question

I need a way to audit when someone attempts to either ENABLE or DISABLE a trigger in our database. The DDL trigger alternative works great but only under the condition for when the user uses

ALTER TABLE <tableName> ENABLE TRIGGER <triggerName>

OR

ALTER TABLE <tableName> DISABLE TRIGGER <triggerName>

statement. From what I have determined, the DDL method renders useless if the user executes the following statements that bypass the ALTER command:

DISABLE TRIGGER <triggerName> ON <tableName>
ENABLE TRIGGER <triggerName> ON <tableName>

I have had several thoughts on capturing these events none of them work. One of which was if I could access the table underlying the sys.triggers view, I could place an insert/update trigger on that table and filter on the trigger name to acquire the audit; but my suspicion is that it would likely lead to an infinite recursion even if it were possible to do.

Does anyone here have any possible suggestions for alternative solutions to this problem? I don't understand why MS would allow enhanced versions of statements to escape the scope of audits. That is, auditing from the most simplest methods; using SQL profiler seems to be unneccesary overhead for this.

Answer 1

I would first address this through permissions. No one except a few dbas should have alter table permissions on prod and thus cannot use enable or disable trigger. If an application is using it, that should stop it. There is no excuse for any user to need to alter tables. If you want to do that, you probaly have a design flaw. If you have people actualy disabling triggers, you definitely have a design flaw. Any code with disable trigger in it should be a huge red flag during a code review. It should be unacceptable to disable a trigger in application code. This is something that should only be done by dbas who have the experience to know when to do it. If you are writing application code and seem to need to disable a trigger to get your code to work, then your code is incorrect or the trigger needs to be rewritten, disabling triggers should only happen in the rarest of cases.