Auditing of DDL (Create, Alter, Drop) Commands

With the growing need to audit activities in our database servers, there is a need to figure out the best approach to collect changes on your system. With SQL Server 2000 we are 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 our database.

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
print 'your statement is logged for auditing purpose'
END

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
print 'your statement is logged for auditing purpose'
END


Here is a listing of the event groups. This list can be found 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.

Next we will see how we can extract information from this DDL Trigger using EVENTDATA() function.