--第一步:建库建表
if exists(select 1 from master.dbo.sysdatabases where name='AuditDB')
drop database AuditDB
go
create database AuditDB
go
use AuditDB
go
if object_id('DDLEvents','u')is not null
drop table DDLEvents
go
create table DDLEvents(
EventDate datetime default (getdate()) NOT null, --事件时间
EventType nvarchar(64) null, --事件类型
EventDDL nvarchar(max) null, --事件内容
EventXML xml null, --事件xml
databaseNamenvarchar(255) null, --数据库名称
SchemaName nvarchar(255) null, --架构名
ObjectName nvarchar(255) null, --用户
HostName varchar(64) null, --计算机名称
IPAddress varchar(32) null, --IP地址
ProgramNamenvarchar(255) null, --SQLServer版本
LoginName nvarchar(255) null --登录名
)
go
--第二步:在需要监控的库上执行这个脚本,对DDL操作会记录在第一步中的库中
if exists(select * from sys.triggers where parent_class_desc= 'database' AND name = N'DDLtriggertTrace')
disable trigger DDLtriggertTraceon database
if exists(select * from sys.triggers where parent_class_desc= 'database' AND name = N'DDLtriggertTrace')
drop trigger DDLtriggertTraceon database
go
create trigger DDLtriggertTraceon database
--捕获存储过程、视图、表的创建、修改、删除动作
for create_procedure, alter_procedure, drop_procedure, create_view,
alter_view, drop_view, create_table, alter_table, drop_table
as
begin
set nocount on ;
declare@EventData xml = eventdata() ;--返回有关服务器或数据库事件的信息,以xml格式保存。
declare@ip varchar(32) =( select client_net_address
from sys.dm_exec_connections
where session_id = @@SPID
) ;
insert AuditDB.dbo.DDLEvents
( EventType ,
EventDDL,
EventXML,
databaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
select @EventData.value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(100)') ,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)') ,
@EventData ,
DB_NAME() ,
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(255)') ,
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(255)') ,
HOST_NAME() ,
@ip ,
PROGRAM_NAME() ,
SuseR_SNAME() ;
end
go