此文章主要是脚本示例，更多说明看官方文档：审核（数据库引擎）
-- 必须在 master 数据库中创建审核USE master;GO-- 创建服务器审核对象-- https://msdn.microsoft.com/zh-cn/library/cc280448(v=sql.100).aspxCREATE SERVER AUDIT [Audit_ToFile]TO FILE ( --目标类型：FILE(文件)/APPLICATION_LOG(应用程序日志)/SECURITY(安全日志) FILEPATH = N'E:\' --审核日志的路径, MAXSIZE = 100MB --审核文件最大大小(MB、GB、TB 或 UNLIMITED), MAX_ROLLOVER_FILES = 5 --最大文件数(或者UNLIMITED), RESERVE_DISK_SPACE = ON --预先分配MAXSIZE大小(MAXSIZE<>UNLIMITED 时适用。默认：OFF))WITH ( QUEUE_DELAY = 1000 --强制审核前时间，默认1000(毫秒)，值 0 指示同步传递, ON_FAILURE = CONTINUE --无法写入目标文件时：CONTINUE(默认) | SHUTDOWN(需要权限)--, AUDIT_GUID = <GUID> --数据库镜像使用)GO-- 更改服务器审核对象（与创建格式一样）-- https://msdn.microsoft.com/zh-cn/library/cc280563%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER SERVER AUDIT [Audit_ToFile]MODIFY NAME = [Audit_ToFile] --更改审核名称GOALTER SERVER AUDIT [Audit_ToFile]WITH (STATE = ON ) --启用审核收集记录GO-- 删除审核(必须禁用审核收集记录)-- https://msdn.microsoft.com/zh-cn/library/cc280899(v=sql.100).aspxALTER SERVER AUDIT [Audit_ToFile]WITH (STATE = OFF )GOIF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'Audit_ToFile')DROP SERVER AUDIT [Audit_ToFile]GO
-- 创建服务器审核规范对象-- https://msdn.microsoft.com/zh-cn/library/cc280767%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396CREATE SERVER AUDIT SPECIFICATION [Audit_Specification_Server]FOR SERVER AUDIT [Audit_ToFile] --应用此规范的审核名称 ADD ( FAILED_LOGIN_GROUP ) --服务器级别可审核操作组的名称 (如 登录失败审核)WITH ( STATE = ON ) --允许或禁止审核收集此审核规范的记录GO-- SQL Server 审核操作组和操作-- https://msdn.microsoft.com/zh-cn/library/cc280663%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396-- 更改服务器审核规范对象(必须将审核规范的状态设置为 OFF 选项)-- https://msdn.microsoft.com/zh-cn/library/cc280682(v=sql.100).aspxALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]WITH ( STATE = OFF ) GOALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]FOR SERVER AUDIT [Audit_ToFile] ADD ( SUCCESSFUL_LOGIN_GROUP ) , ADD ( LOGOUT_GROUP ), DROP ( LOGIN_CHANGE_PASSWORD_GROUP )WITH ( STATE = ON ) GO/* 必须将审核规范的状态设置为 OFF 选项，以便更改审核规范,否则出现错误:消息 33229，级别 16，状态 1，第 1 行禁用审核规范时，对审核规范的更改必须已完成。*/-- 删除服务器审核规范对象(必须将审核规范的状态设置为 OFF 选项)-- https://msdn.microsoft.com/zh-cn/library/cc280603%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]WITH ( STATE = OFF ) GOIF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'Audit_Specification_Server')DROP SERVER AUDIT SPECIFICATION [Audit_Specification_Server]GO
-- 创建数据库审核规范对象 (只对当前数据库创建审核)-- https://msdn.microsoft.com/zh-cn/library/cc280404%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396USE AdventureWorks2008R2;GOCREATE DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]FOR SERVER AUDIT [Audit_ToFile] ADD (SCHEMA_OBJECT_CHANGE_GROUP)WITH ( STATE = ON )GOCREATE DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]FOR SERVER AUDIT [Audit_ToFile] ADD ( SELECT ON dbo.ErrorLog BY [dbo])WITH ( STATE = ON )GO-- 更改数据库审核规范对象 (必须禁止审核收集此审核规范的记录)-- https://msdn.microsoft.com/zh-cn/library/cc280645%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]WITH ( STATE = OFF ) GOALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]FOR SERVER AUDIT [Audit_ToFile] ADD ( UPDATE,INSERT,DELETE ON dbo.ErrorLog BY [dbo]), DROP ( SELECT ON dbo.ErrorLog BY [dbo])WITH ( STATE = ON )GO-- 删除数据库审核规范对象 (必须禁止审核收集此审核规范的记录)-- https://msdn.microsoft.com/zh-cn/library/cc280479%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]WITH ( STATE = OFF ) GOIF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N'Audit_Specification_Database')DROP DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]GO
-- 当删除数据库AdventureWorks2008R2的表ErrorLog时delete from dbo.ErrorLog-- 审核日志记录如下
-- 相关视图查询select * from sys.server_audits --审核对象select * from sys.dm_server_audit_status --审核的当前状态select * from sys.server_audit_specifications --服务器审核规范对象select * from sys.server_audit_specification_details --服务器审核规范对象的审核操作select * from sys.database_audit_specifications --数据库审核规范对象select * from sys.database_audit_specification_details --数据库审核规范对象的审核操作select * from sys.dm_audit_actions --所有可用的操作组select * from sys.dm_audit_class_type_map --审核日志中的字段映射select * from sys.server_file_audits --审核文件信息select * from sys.fn_get_audit_file('E:\Audit_ToFile_*.sqlaudit',default,default);