服务器作用域的
DDL
语句——能够审计的相关类别
-- 创建一张审计表，也可以为每类审计定制相关审计表
CREATE TABLE dbo.AuditEventsTable
(
ID INT NOT NULL IDENTITY,
EventType SYSNAME NOT NULL,
PostTime DATETIME NOT NULL,
SPID SYSNAME NOT NULL,
ServerName SYSNAME NOT NULL,
LoginName SYSNAME NOT NULL,
UserName SYSNAME NOT NULL,
DatabaseName SYSNAME NOT NULL,
SchemaName SYSNAME NOT NULL,
ObjectName SYSNAME NOT NULL,
ObjectType SYSNAME NOT NULL,
CommandText SYSNAME NOT NULL,
EventData XML NOT NULL,
Flag INT,
MSG VARCHAR(500),
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(ID)
);
GO
-- 所有审计数据均存在在一个 XML 中，相关结构如下
EVENTDATA 数据构成
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2010-03-17T15:59:46.687</PostTime>
<SPID>57</SPID>
<ServerName>SQLSERVER\TEST2005</ServerName>
<LoginName>sa</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>TETS</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TABLE TETS(AA VARCHAR(20))</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
-- 根据每种审计，创建相应的审计触发器，该触发器可以基于数据库也可以基于服务器
ALTER TRIGGER TRI_AUDIT_CREATE_TABLE ON DATABASE FOR CREATE_TABLE
AS
DECLARE @EventData XML;
DECLARE @ObjectName SYSNAME;
DECLARE @MSG VARCHAR(500);
SET @EventData=EVENTDATA();
SET @ObjectName=
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME')+'.'+
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME');
--此处可以进行相关审核
IF OBJECTPROPERTY(OBJECT_ID(@ObjectName),'TableHasPrimaryKey')=0
BEGIN
SET @MSG='Table
'+@ObjectName+' does not contain a primary key, You can''t create it';
RAISERROR(@MSG,16,1);
ROLLBACK
/*
INSERT INTO dbo. AuditEventsTable
(EventType,PostTime,SPID,ServerName,LoginName,UserName,DatabaseName,
SchemaName,ObjectName,ObjectType,CommandText,EventData,Flag,MSG
)
VALUES
(
@EventData.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(23)'),
@EventData.value('(/EVENT_INSTANCE/PostTime)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/SPID)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/ServerName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/LoginName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/UserName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','SYSNAME'),
@EventData,
0,
@MSG
);
*/
RETURN;
END
ELSE
-- 记录成功日志
INSERT INTO dbo. AuditEventsTable
(EventType,PostTime,SPID,ServerName,LoginName,UserName,DatabaseName,
SchemaName,ObjectName,ObjectType,CommandText,EventData,Flag
)
VALUES
(
@EventData.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(23)'),
@EventData.value('(/EVENT_INSTANCE/PostTime)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/SPID)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/ServerName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/LoginName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/UserName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','SYSNAME'),
@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]','SYSNAME'),
@EventData,
1
);
GO
-- 进行相关测试
create table test(a varchar(20))
SELECT * FROM dbo. AuditEventsTable
参考：
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/27569888-f8b5-4cec-a79f-6ea6d692b4ae.htm
本文转自baoqiangwang51CTO博客，原文链接：，如需转载请自行联系原作者
