Trigger Happy

  • Andy Jones, DBA

    Hall of Fame

    Points: 3641

    Comments posted to this topic are about the item Trigger Happy

    .

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the article.

  • Alan Burstein

    SSC Guru

    Points: 61006

    Great article Andy, well done.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

  • petitpere

    SSCrazy

    Points: 2520

    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

  • Nelson 2.0

    Mr or Mrs. 500

    Points: 500

    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

  • Nelson 2.0

    Mr or Mrs. 500

    Points: 500

    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.

  • Andy Jones, DBA

    Hall of Fame

    Points: 3641

    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.

    .

  • raymondbarley

    Grasshopper

    Points: 23

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

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

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