SQL Server 的DDL操作审计触发器简明指南

    Sql Server 触发器，记录数据库中DDL操作记录，用于数据库结构变更审计。

    1、创建一张table，用于保存DDL操作记录:

USE [DBNAME]
GO

CREATE TABLE [dbo].[DatabaseLog](
[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [nvarchar](max) NOT NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
[DatabaseLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

   2、创建数据库触发器，将结构变更记录写入DatabaseLog表:

USE [DBNAME]
GO

CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;

DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;

SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;

IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);

INSERT [dbo].[DatabaseLog]
(
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(
GETDATE(),
CONVERT(sysname, CURRENT_USER),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
@data
);
END;
GO

ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
GO