SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution

  • Sean Elliott (UK) (8/21/2010)

    Have you ever automatically generated the script for a SQL Agent Job in SQL Server Management Studio? When you do this it includes GOTO statements. I've got bigger fish to fry than converting automatically generated scripts! 😛

    Nope, I never have. Well then, if Management Studio generates it, I am once again disappointed with MicroSoft and how they do things.

  • I must say, I'm quite impressed. This appears to be the most complete auditing setup that utilizes DDL triggers. Well done, and thank you for the excellent documentation! 😀

    [font="Arial Black"]My server's the fastest hunk of junk in the galaxy! -Han SQL
    Follow on Twitter: @HanSQL[/font]

  • Glad you like it. I was quite impressed with myself too 🙂

  • Shortly there will be 2 extra scripts and an associated update to the documentation. These scripts are for uninstalling and re-installing DDL audit capability for one database only. The scripts also disable and re-enable the automatic install of DDL auditing for the server.

  • I have made some modification to your script. To bad but the environment I am in is using database names with spaces in it. This is the reason I had to update your script a little bit. Nothing mayor, but in order to make it work I have added [] around the ? in command to execute sp_MSForEachDB. See the script below. I know it is a bad thing to have a database with spaces in the name, but what can I do if you are getting into a environment without any standards before I came there. 😛


    -- SQL 2005 DDL Auditing Solution


    -- Stand up test for all database level auditing and server auditing


    -- Sean Elliott

    -- sean_p_elliott@yahoo.co.uk


    -- July 2010


    select * from dbadata..ServerAudit

    exec sp_MSForEachDb 'if ''[?]'' != ''[tempdb]'' begin use [?];print ''[?]'';select * from DatabaseAudit end'

    exec sp_MSForEachDB 'use [?]; print ''[?]''; create table aa_test_ddl_audit (col1 int)'

    exec sp_MSForEachDB 'use [?]; print ''[?]''; drop table aa_test_ddl_audit'

Viewing 5 posts - 16 through 19 (of 19 total)

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