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

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

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

  • Jurgen-494374

    SSC Journeyman

    Points: 90

    is renaming an object (sp_rename) also audited ?

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    SQL2008 Yes

    SQL2005 No

  • shaun.stuart

    SSCertifiable

    Points: 6695

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

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    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.

  • SamElston

    SSC Enthusiast

    Points: 105

    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

  • SQLRNNR

    SSC Guru

    Points: 281205

    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

  • jonathanmoss98

    Valued Member

    Points: 58

    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

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    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.

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

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

  • SamElston

    SSC Enthusiast

    Points: 105

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

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    Good stuff

  • jonathanmoss98

    Valued Member

    Points: 58

    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.

  • Adam Gojdas

    SSCommitted

    Points: 1766

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

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    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 20 total)

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