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 Monday, August 16, 2010 10:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:15 PM
Points: 42, Visits: 177
Comments posted to this topic are about the item SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution
Post #970133
Posted Tuesday, August 17, 2010 2:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 08, 2011 2:22 AM
Points: 12, Visits: 67
is renaming an object (sp_rename) also audited ?
Post #970182
Posted Tuesday, August 17, 2010 5:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:15 PM
Points: 42, Visits: 177
SQL2008 Yes
SQL2005 No


Post #970251
Posted Tuesday, August 17, 2010 8:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:08 AM
Points: 1,234, Visits: 717
This is genius!! Testing it out now. Thanks!
Post #970465
Posted Tuesday, August 17, 2010 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:15 PM
Points: 42, Visits: 177
You may need some additional permissions/config if your server does not already have them:

exec sp_configure ‘show advanced options’, 1
exec sp_configure ‘Agent XPs’, 1

For reasons unknown you may also need to add exec permissions explicitly to server_audit user in the master database for:

xp_sqlagent_is_starting
xp_sqlagent_notify

Both of these xp are called by sp_start_job and server_audit should have implicit exec permission via membership of msdb SQLAgentOperatorRole and cross db ownership chaining (always on for msdb and master). In general I have not needed to add these permissions but on some SQL2005 servers (so far) I have needed to. Seems strange because as far as I can see the DDL code is the same and so are the permissions.
Post #970512
Posted Tuesday, August 17, 2010 1:53 PM
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
Where are these scripts !?

install_ddl_auditing.sql and uninstall_ddl_auditing.sql

I unzipped the 2008 files - and seem to be missing major components !??

thanks
Post #970697
Posted Tuesday, August 17, 2010 2:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:16 AM
Points: 20,465, Visits: 14,095
Thanks for the article. I will need to bookmark this and look over it a few more times.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #970739
Posted Tuesday, August 17, 2010 10:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 02, 2013 3:24 PM
Points: 2, Visits: 51
I tried this out, and fell over at the second step, given I did not have a dbadata database already existing in my environment. I could run the 'install' script but not the 'enable' script on SQL 2005.

Once I manually created the dbadata database, and reran the 'install' script I could continue all OK!

Is this requirement for the dbadata database to already exist an omission, or an is it an assumption that you would manually create the dbadata database first?

If it's an omission, then can I please recommend it is added to the doc, as this is an extremely useful post that saved me and my colleague a lot of time and wasted energy.

Regards, Jonathan

Post #970885
Posted Wednesday, August 18, 2010 3:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:15 PM
Points: 42, Visits: 177
It already explains in the article that dbadata database needs to exist in advance. Alternatively you can globally replace dbadata with another "dba database" you already have on the server.
Post #971001
Posted Wednesday, August 18, 2010 3:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 20, 2014 1:15 PM
Points: 42, Visits: 177
Sorry Sam but you are mistaken. All the scripts are in the zip file and always were.
Post #971002
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse