<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Sean Elliott
»
SQL 2008 & 2005 DDL Auditing - Full...
20 posts, Page 2 of 2
««
1
2
SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution
Rate Topic
Display Mode
Topic Options
Author
Message
SamElston
SamElston
Posted Wednesday, August 18, 2010 11:28 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, December 11, 2012 9:31 AM
Points: 5,
Visits: 77
OK - I had to create the DBA database - now it seems to be working.
Post #971330
Sean Elliott (UK)
Sean Elliott (UK)
Posted Wednesday, August 18, 2010 12:06 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
Good stuff
Post #971348
jonathan.moss
jonathan.moss
Posted Wednesday, August 18, 2010 5:04 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, September 19, 2012 5:02 PM
Points: 2,
Visits: 49
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.
Post #971525
Adam Gojdas
Adam Gojdas
Posted Thursday, August 19, 2010 7:59 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79,
Visits: 1,317
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).
Post #971890
Sean Elliott (UK)
Sean Elliott (UK)
Posted Saturday, August 21, 2010 3:46 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
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!
Post #972922
Adam Gojdas
Adam Gojdas
Posted Sunday, August 22, 2010 2:27 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79,
Visits: 1,317
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.
Post #973106
Han SQL
Han SQL
Posted Tuesday, August 24, 2010 5:48 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, May 20, 2013 5:41 PM
Points: 117,
Visits: 164
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!
My server's the fastest hunk of junk in the galaxy! -Han SQL
Follow on Twitter: @HanSQL
Post #974546
Sean Elliott (UK)
Sean Elliott (UK)
Posted Wednesday, August 25, 2010 3:09 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
Glad you like it. I was quite impressed with myself too
Post #974694
Sean Elliott (UK)
Sean Elliott (UK)
Posted Monday, April 04, 2011 4:33 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
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.
Post #1088344
jacobbuter 3956
jacobbuter 3956
Posted Thursday, March 08, 2012 2:43 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 20, 2013 9:22 AM
Points: 5,
Visits: 138
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'
Post #1263965
« Prev Topic
|
Next Topic »
20 posts, Page 2 of 2
««
1
2
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
