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
-- 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)