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

Question

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

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

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

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 1

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.

Han SQL SSC Enthusiast Points: 145 More actions · Answer 2

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]

Sean Elliott (UK) SSCommitted Points: 1626 More actions · Answer 3

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

Sean Elliott (UK) SSCommitted Points: 1626 More actions · Answer 4

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.

jacobbuter 3956 SSC Enthusiast Points: 169 More actions · Answer 5

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'