SQL Server 2005 – DDL Triggers – Setting Up Auditing

New with SQL Server 2005, is the ability to set up triggers on DDL statements. This has got to be a DBA’s dream come true. You can set up audit logs on creates/alters, etc. Here is how you do it (this is just a trigger, you will need to create the table, you should be able to get the table definitiion from looking at the trigger)

CREATE TRIGGER [DDL_Auditing] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @raisedEventData XML /* type date-time spid name name name name name name type command */ -- Capture the event data that is created SET @raisedEventData = eventdata() INSERT INTO dbo.AuditDDL (EventTime,EventType,ServerName,CommandText,LoginName,UserName,ObjectType,ObjectName) SELECT @raisedEventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)') AS 'EventTime', @raisedEventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') AS 'EventType', @raisedEventData.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)') AS 'ServerName', @raisedEventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(MAX)') AS 'CommandText', @raisedEventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') AS 'LoginName', @raisedEventData.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') AS 'UserName', @raisedEventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') AS 'ObjectType', @raisedEventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') AS 'ObjectName'