Post reply

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

  • August 16, 2010 at 10:26 pm

    #225956

    Comments posted to this topic are about the item SQL 2008 & 2005 DDL Auditing - Full Server Self Maintaining Solution

  • August 17, 2010 at 2:11 am

    #1207691

    is renaming an object (sp_rename) also audited ?

  • August 17, 2010 at 5:07 am

    #1207739

    SQL2008 Yes

    SQL2005 No

  • August 17, 2010 at 8:37 am

    #1207919

    This is genius!! Testing it out now. Thanks!

  • August 17, 2010 at 9:23 am

    #1207961

    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.

  • August 17, 2010 at 1:53 pm

    #1208116

    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

  • August 17, 2010 at 2:50 pm

    #1208153

    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, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 17, 2010 at 10:26 pm

    #1208277

    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

  • August 18, 2010 at 3:37 am

    #1208371

    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.

  • August 18, 2010 at 3:41 am

    #1208372

    Sorry Sam but you are mistaken. All the scripts are in the zip file and always were.

  • August 18, 2010 at 11:28 am

    #1208662

    OK - I had to create the DBA database - now it seems to be working.

  • August 18, 2010 at 12:06 pm

    #1208676

    Good stuff

  • August 18, 2010 at 5:04 pm

    #1208823

    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.

  • August 19, 2010 at 7:59 am

    #1209127

    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). 🙁

  • August 21, 2010 at 3:46 am

    #1210002

    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! 😛

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply