There is a big security hole in SQL Server 2008, that we discovered : there is this new syntax "DISABLE TRIGGER" (as opposed to legacy syntax ALTER TABLE DISABLE TRIGGER).
We discovered that this new syntax, contrary to any other DDL statement :
- does not fire any DDL TRIGGER (therefore we can't audit not forbid the usage)
- is not caught in the profiler (therefore we don't know who uses it)
This seems to be "as designed" in the product (see https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=434951&lc=1033&id=64416&wa=wsignin1.0 and affiliates )
This is unacceptable in a professional environment, and ruins any security model based on "Management by policy" (and not only for us : others on the internet are claiming this is a bug)
The proposed workarounds that we have read, are ranging from "discipline" of "not using this syntax" (easier said than done, since DbA cannot control nor enforce this discipline !). Even more asinine would be to upgrade to Enterprise Edition to be able to audit this... More constructive is to revoke the entire "ALTER" right for persons not supposed to do this. However this is _really_ overkilling, and is not acceptable either, when DbA want to delegate part of the Release Management to the Application department, and control them using DDL Triggers.