DDL trigger for auditing DDL statments running on SQL Server

Today I would discuss the DDL triggers available in SQL Server 2005. Like Oracle these are executed when any DDL statement is executed. The SQL statements can be logged in this trigger.

For keeping a record, it is better to create a table.

CREATE TABLE [dbo].[DDL_OBJECT_VERSION](
[EVENT_TYPE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[POST_TIME] [datetime] NULL,
[LOGIN_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[USERNAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATABASE_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SCHEMA_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[OBJECTNAME] [nchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OBJECT_TYPE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SQL_TEXT] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SERVER_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

The context information can be obtained by EVENTDATA() function availble in DDL trigger. The format of XML returned by this function is as follows:


CREATE_TABLE
2005-07-30T10:48:52.537
55
TEST-SHUJAAT
shujaat\Administrator
dbo
AdventureWorks
Person
Address
TABLE

ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON"
ENCRYPTED="FALSE" />
CREATE TABLE [Person].[Address](
[AddressID] [int]
IDENTITY (1, 1)
NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[StateProvinceID] [int] NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF_Address_rowguid] DEFAULT (NEWID()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT
[DF_Address_ModifiedDate] DEFAULT (GETDATE())
) ON [PRIMARY];



You can access invidual information with XQuery support available in SQL Server. The example code of DDL trigger is as follows:

CREATE TRIGGER [DDL_TRIG_OBJ_VERSIONS]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE @data XML;
SET @data = EVENTDATA();
insert INTO [DBO].[DDL_OBJECT_VERSION](
EVENT_TYPE,
POST_TIME,
SERVER_NAME,
LOGIN_NAME,
USERNAME,
DATABASE_NAME,
SCHEMA_NAME,
OBJECTNAME,
OBJECT_TYPE,
SQL_TEXT
)
VALUES(
@data.value('(/EVENT_INSTANCE/EventType)[1]','sysname'),
GETDATE(),
@data.value('(/EVENT_INSTANCE/ServerName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/UserName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/SchemaName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]','sysname'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','VARCHAR(max)')
);