<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 1 of 2
1
2
»»
SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution
Rate Topic
Display Mode
Topic Options
Author
Message
Sean Elliott (UK)
Sean Elliott (UK)
Posted Monday, August 16, 2010 10:26 PM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
Comments posted to this topic are about the item
SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution
Post #970133
Jurgen-494374
Jurgen-494374
Posted Tuesday, August 17, 2010 2:11 AM
Grasshopper
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
Sean Elliott (UK)
Sean Elliott (UK)
Posted Tuesday, August 17, 2010 5:07 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
SQL2008 Yes
SQL2005 No
Post #970251
shaun.stuart
shaun.stuart
Posted Tuesday, August 17, 2010 8:37 AM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:21 AM
Points: 964,
Visits: 632
This is genius!! Testing it out now. Thanks!
Post #970465
Sean Elliott (UK)
Sean Elliott (UK)
Posted Tuesday, August 17, 2010 9:23 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
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
SamElston
SamElston
Posted Tuesday, August 17, 2010 1:53 PM
Forum 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
SQLRNNR
SQLRNNR
Posted Tuesday, August 17, 2010 2:50 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 18,733,
Visits: 12,330
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 2008
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
jonathan.moss
jonathan.moss
Posted Tuesday, August 17, 2010 10:26 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, September 19, 2012 5:02 PM
Points: 2,
Visits: 49
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
Sean Elliott (UK)
Sean Elliott (UK)
Posted Wednesday, August 18, 2010 3:37 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
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
Sean Elliott (UK)
Sean Elliott (UK)
Posted Wednesday, August 18, 2010 3:41 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42,
Visits: 170
Sorry Sam but you are mistaken. All the scripts are in the zip file and always were.
Post #971002
« Prev Topic
|
Next Topic »
20 posts, Page 1 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.
