Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SamElston
SamElston
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 77
OK - I had to create the DBA database - now it seems to be working.
Sean Elliott (UK)
Sean Elliott (UK)
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 181
Good stuff
jonathan.moss
jonathan.moss
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 51
Sean,
Thankyou, I see it now in the Introduction of the article where is states the database must already exist. It pays to read properly, clearly.

Incidently, this line is not in the doc in the zip file, which is the one I read through after quickly reviewing the article itself.

In any case, it is working for me now.
Adam Gojdas
Adam Gojdas
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1429
Cool stuff, this type of auditing can really come in handy.

However, I am surprised to see @@ERROR\GOTO used as the error handling mechanism in these scripts instead of TRY\CATCH which has been available in 2005 and up (maybe for 6 years now). Sad
Sean Elliott (UK)
Sean Elliott (UK)
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 181
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! :-P
Adam Gojdas
Adam Gojdas
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 1429
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! :-P


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
Han SQL
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 222
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! :-D

My server's the fastest hunk of junk in the galaxy! -Han SQL
Follow on Twitter: @HanSQL

Sean Elliott (UK)
Sean Elliott (UK)
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 181
Glad you like it. I was quite impressed with myself too :-)
Sean Elliott (UK)
Sean Elliott (UK)
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 181
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
jacobbuter 3956
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 216
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. :-P

--
-- 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'
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search