Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005

Problem

With the growing need to audit activity in your database servers there is a need to figure out the best approach to collect changes on your system.  There are several requirements these days such as Sarbanes Oxley, SAS70, GLBA, etc... that require you to keep a close eye on your database activity.  With SQL Server 2000 you were limited to auditing login information and capturing data changes using DML (Data Manipulation Language) triggers, but there was no easy way to track DDL (Data Definition Language) changes in your database.  As always there are third party tools that simplify the process, but most of the time the initial look is at what can be done natively with SQL Server.

Solution

With SQL Server 2005 DDL (Data Definition Language) triggers have been introduced.  This type of trigger is different then INSERT, UPDATE and DELETE triggers, this trigger is fired when a change is made using such commands as ALTER, CREATE or DROP.  The trigger is fired after the event occurs, so there is not an INSTEAD of trigger option like you have with DML triggers. 

Creating a DDL trigger is just as simple as creating a DML trigger.  Here is an example of a trigger that would fire whenever there is a DROP_TABLE or ALTER_TABLE event.

CREATE TRIGGER tr_tableChange ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN     do some activity based on event END

Below is a sample listing of some of the events that can be captured. The list is much more extensive then this and can be found here or in SQL Server 2005 Books Online.

Transact-SQL statement	Can be server scope (ON SERVER)	Can be database scope (ON DATABASE)
ADD_ROLE_MEMBER	X	X
ADD_SERVER_ROLE_MEMBER	X
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.)	X	X
ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)	X	X
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)	X	X
CREATE_ASSEMBLY	X	X
ALTER_ASSEMBLY	X	X
DROP_ASSEMBLY	X	X
ALTER_AUTHORIZATION_SERVER	X
ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)	X	X
CREATE_CERTIFICATE	X	X
ALTER_CERTIFICATE	X	X
DROP_CERTIFICATE	X	X
CREATE_CONTRACT	X	X
DROP_CONTRACT	X	X
CREATE DATABASE	X
ALTER DATABASE	X	X
DROP DATABASE	X

source: SQL Server 2005 Books Online

In addition to individual events, DDL events can be collected by using event groups.  This example here is triggered whenever there is DDL_LOGIN_EVENTS action.  This includes DROP_LOGIN, CREATE_LOGIN and ALTER_LOGIN.

CREATE TRIGGER tr_LoginEvents ON ALL SERVER FOR DDL_LOGIN_EVENTS AS BEGIN     do some activity based on event END

Here is a listing of the event groups. This list can be found here or look in SQL Server 2005 Books Online.  The way this works is that each group consists of individual events.  Also, some event groups contain other event groups.  So for example the DDL_SERVER_LEVEL_EVENTS would capture all events that occur on the server.

Source Collected from MSSQLTIPS.COM