SQL Server audit DDL operations

 CREATE TABLE AuditDDLEvents

(

LSN              INT      NOT NULL IDENTITY,

posttime         DATETIME NOT NULL,

eventtype        SYSNAME  NOT NULL,

loginname        SYSNAME  NOT NULL,

schemaname       SYSNAME  NOT NULL,

objectname       SYSNAME  NOT NULL,

targetobjectname SYSNAME  NOT NULL,

eventdata        XML      NOT NULL,

CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)

)

GO

CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR

DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @eventdata AS XML

SET @eventdata = eventdata()

INSERT INTO dbo.AuditDDLEvents(

posttime, eventtype, loginname, schemaname,

objectname, targetobjectname, eventdata)

VALUES(

CAST(@eventdata.query('data(//PostTime)')         AS VARCHAR(23)),

CAST(@eventdata.query('data(//EventType)')        AS SYSNAME),

CAST(@eventdata.query('data(//LoginName)')        AS SYSNAME),

CAST(@eventdata.query('data(//SchemaName)')       AS SYSNAME),

CAST(@eventdata.query('data(//ObjectName)')       AS SYSNAME),

CAST(@eventdata.query('data(//TargetObjectName)') AS SYSNAME),

@eventdata)

GO

-- Test

CREATE TABLE Test (cl varchar(20))

DROP TABLE Test 

SELECT * FROM AuditDDLEvents

DROP  TRIGGER trg_audit_ddl_events ON DATABASE

DROP  TABLE AuditDDLEvents