Trigger Happy

  • Comments posted to this topic are about the item Trigger Happy


  • Thanks for the article.

  • Great article Andy, well done.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • English is not my mother tongue, but I definitely liked the way you wrote this article 😎

    Of course thanks for the tips and we currently don't audit the logins. It's all a matter of priorities 😉


  • Hello!

    Thank you for the article, that DDL trigger have made my boss (and me) happy auditing "who did what to the production systems" (or even the dev ones) for a good while now.

    I would like to raise a concern, though, about which account you use when creating the trigger (the one that is going to be its owner).

    If you use your own Windows account via Windows authentication (and not the sa account, which shouldn't be enabled anyway), and your password expires, the trigger will fail. And you don't want all your DDL commands, like re-indexing to start with, to start failing.

    So at the start of the script, I have put EXECUTE AS LOGIN = 'sa', with a corresponding REVERT at the end.

    There must be a way to use ALTER AUTHORIZATION as well, but so far I only used option 1.


  • Ah, sorry, I missed the WITH EXECUTE AS 'Auditor' which is a fix to my version of the trigger (which is very close to the one exposed here).

    The script I used has EXECUTE as OWNER, which is troublesome indeed.

    My humble apologies.

  • Hi Nelson,

    No problem and I hope you found the trigger useful.

    Yes, the trigger executes as Auditor, a user that has the minimum level of permissions required to insert to the audit table.

    You can of course change this to suit your needs. One addendum to the article is adding "GRANT SHOWPLAN TO db_auditor;" if you wish to view execution plans that include the triggering action.

    Many thanks for your interest, Andy.


  • The Dirty Harry reference in the intro "made my day"!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply