Audit all server DDL level events

• kevaburg

  SSCoach

  Points: 18131

  More actions

  March 6, 2015 at 11:02 am

  #398139

  Hi Folks,

  I have been tasked with auditing all DDL and selected DML events on a production server and logging them to a table. My solution is to use CDC for the DML and a Server-Level trigger for the DDL. Because there should never but much DDL activity on the server (except when performing update tasks) I don't need to worry about the trigger consuming too many resources.

  My question is this: Is anyone aware of a single specification such as DDL_LEVEL_EVENTS that can capture all DDL activity or do I need to specify each and every DDL action in the trigger?

  Many thanks.

  Kev

• seaport

  SSCrazy

  Points: 2491

  More actions

  March 9, 2015 at 8:53 am

  #1782565

  I've been using the script from this blog http://sqlknowledge.com/tag/ddl_database_level_events/ and have not got any issues.

  Another good resource is:

  http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

  Please note:

  1. The DDL trigger is "After Event" trigger.

  2. To catch all object definitions, initial insert of all object definitions is needed.