You are here:Home » tsql » DDL Auditing

DDL Auditing

CREATE TABLE [dbo].[DDLEventLog](
[EventDate] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[objectName] [sysname] NOT NULL,
[CommandText] [varchar](MAX) NOT NULL,
[EventType] [nvarchar](100) NULL
)
--
GO
CREATE TRIGGER [ReturnTBEventData]
ON DATABASE
FOR
CREATE_TABLE, DROP_TABLE, ALTER_TABLE

AS
DECLARE @eventData XML,
@uname NVARCHAR(50),
@oname NVARCHAR(100),
@otext VARCHAR(MAX),
@etype NVARCHAR(100),
@edate DATETIME
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @uname IS NOT NULL
BEGIN
INSERT dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) VALUES
(@edate,@uname,@oname,@otext,@etype)
END
GO
ENABLE TRIGGER [ReturnTBEventData] ON DATABASE

--query to populate the original default values:
SELECT
sysobjects.[name] AS 'Table Name',
syscomments.colid AS seq,
syscomments.TEXT
FROM sysobjects
INNER JOIN syscomments ON syscomments.id = sysobjects.[id]
WHERE sysobjects.xtype IN('P','V','TR','IF''FN','TF')
ORDER BY sysobjects.[name],syscomments.colid