Post reply

Trigger Happy

  • March 28, 2016 at 12:23 am

    #326127

    Comments posted to this topic are about the item Trigger Happy

    .

  • March 28, 2016 at 6:57 am

    #1867947

    Thanks for the article.

  • March 28, 2016 at 9:18 am

    #1867997

    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

  • March 30, 2016 at 9:26 am

    #1868653

    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 😉

    Thanks

  • March 31, 2016 at 2:23 am

    #1868771

    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.

    Cheers

  • March 31, 2016 at 9:57 am

    #1868936

    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.

  • March 31, 2016 at 11:43 am

    #1868968

    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.

    .

  • October 12, 2018 at 6:46 am

    #2009359

    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