SqlServer 审核（脚本示例）

此文章主要是脚本示例，更多说明看官方文档：审核（数据库引擎）

--	必须在 master 数据库中创建审核
USE master;
GO


--	创建服务器审核对象
--	https://msdn.microsoft.com/zh-cn/library/cc280448(v=sql.100).aspx
CREATE 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=-2147217396
ALTER SERVER AUDIT [Audit_ToFile]
MODIFY NAME = [Audit_ToFile]		--更改审核名称
GO

ALTER SERVER AUDIT [Audit_ToFile]
WITH (STATE = ON )					--启用审核收集记录
GO


--	删除审核(必须禁用审核收集记录)
--	https://msdn.microsoft.com/zh-cn/library/cc280899(v=sql.100).aspx
ALTER SERVER AUDIT [Audit_ToFile]
WITH (STATE = OFF )
GO

IF  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=-2147217396
CREATE 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).aspx
ALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]
WITH ( STATE = OFF )	
GO

ALTER 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=-2147217396
ALTER SERVER AUDIT SPECIFICATION [Audit_Specification_Server]
WITH ( STATE = OFF )	
GO

IF  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=-2147217396
USE AdventureWorks2008R2;
GO

CREATE DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]
FOR SERVER AUDIT [Audit_ToFile] 
	ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH ( STATE = ON )
GO

CREATE 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=-2147217396
ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]
WITH ( STATE = OFF )	
GO

ALTER 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=-2147217396
ALTER DATABASE AUDIT SPECIFICATION [Audit_Specification_Database]
WITH ( STATE = OFF )	
GO

IF  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);