Audit DDL Operations

One of the great new features in SQL Server 2005 is the ability to create database triggers. Using this new functionality, you can create a table to store all DDL changes done to a given DB along with what the change was, who did it, and when.

You may want to keep this table stored in a separate DB, perhaps adding a column for which DB had the change occur. The WITH ENCRYPTION allows you to encrypt the trigger, so that prying eyes can not see what it is doing and either edit the trigger or find a way around it.

The code:
CREATE TABLE AuditDDLOperations
(
OpID int NOT NULL identity
CONSTRAINT AuditDDLOperationsPK
PRIMARY KEY CLUSTERED,
LoginName sysname NOT NULL,
UserName sysname NOT NULL,
PostTime datetime NOT NULL,
EventType nvarchar(100) NOT NULL,
DDLOp nvarchar(2000) NOT NULL
)
GO

CREATE TRIGGER AuditAllDDL
ON DATABASE
–WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT AuditDDLOperations
(LoginName, UserName, PostTime, EventType, DDLOp)
VALUES (SYSTEM_USER, CURRENT_USER, GETDATE()
, @data.value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(100)’)
, @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]‘, ‘nvarchar(2000)’) )
RETURN
GO