Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution Expand / Collapse
Author
Message
Posted Wednesday, August 18, 2010 11:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 18, 2010 12:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
Good stuff
Post #971348
Posted Wednesday, August 18, 2010 5:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 2, 2013 3:24 PM
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.
Post #971525
Posted Thursday, August 19, 2010 7:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:43 AM
Points: 79, Visits: 1,329
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
Posted Saturday, August 21, 2010 3:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
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
Posted Sunday, August 22, 2010 2:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 9:43 AM
Points: 79, Visits: 1,329
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
Posted Tuesday, August 24, 2010 5:48 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:24 PM
Points: 118, Visits: 180
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
Posted Wednesday, August 25, 2010 3:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
Glad you like it. I was quite impressed with myself too
Post #974694
Posted Monday, April 4, 2011 4:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 8:59 AM
Points: 42, Visits: 178
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
Posted Thursday, March 8, 2012 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:44 AM
Points: 5, Visits: 173
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 »

Add to briefcase ««12

Permissions Expand / Collapse