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

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

  • is renaming an object (sp_rename) also audited ?

  • SQL2008 Yes

    SQL2005 No

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

  • 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.

  • 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

  • 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

  • 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

  • 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.

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

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

  • Good stuff

  • 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.

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

  • 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