---创建审计及审计规范
USE master
CREATE SERVER AUDIT my_first_audit TO FILE(FILEPATH='D:\SQL\TEST\',MAXSIZE=10MB)
GO
---创建和配置可以用于审计的 SQL Server 审计对象
USE Test
CREATE DATABASE AUDIT SPECIFICATION my_aud_spec FOR SERVER AUDIT my_first_audit ADD (SELECT,UPDATE,INSERT,DELETE,EXEC,REFERENCES ON dbo.T_A BY Public) ---本次使用的数据库级别的审核操作(审核级别操作有;服务器级别、数据库级别、审核级别)
go
----查看审计是否存在，并存在的路径
USE master
SELECT * FROM sys.server_file_audits
USE TEST
--审计规范是否存在及其状态
SELECT * FROM sys.database_audit_specifications
-- 启用审计和审计规范（即 是否允许或禁止审核收集此审核规范的记录。）
USE master
ALTER SERVER AUDIT my_first_audit WITH (STATE=ON)
GO
USE Test
ALTER DATABASE AUDIT SPECIFICATION my_aud_spec WITH (STATE=ON)
go
------------执行相关的SQL语句
SELECT * FROM t_a
DELETE FROM t_a WHERE ID=3
update t_a set name='审计' where id=2
---查看审计收集的记录数据
SELECT * FROM fn_get_audit_file ('D:\SQL\TEST\*',NULL, NULL)
go
一个完整的审核案例
---创建审核对象
USE [master]
GO
CREATE SERVER AUDIT [my_first_audit]
TO FILE
( FILEPATH = N'D:\test_audit'
,MAXSIZE = 0 MB ---文件大小无限制
,MAX_ROLLOVER_FILES = 2147483647 --滚动文件大小无限制
,RESERVE_DISK_SPACE = OFF--关闭保留硬盘空间
)
WITH
( QUEUE_DELAY = 1000 ---队列延迟时间 MS为单位
,ON_FAILURE = CONTINUE ---审核日志故障时不关闭服务器（continue|shutdown）
)
---审核目标：FILE\SECURITY LOG\APPLICATION LOG
GO
---服务器审核规范
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
FOR SERVER AUDIT [my_first_audit]
ADD (BACKUP_RESTORE_GROUP), ---审核操作类型
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (TRACE_CHANGE_GROUP)
---具体审核操作类型：见联机丛书
GO
---启用服务器审核规范
ALTER SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
WITH (STATE = ON)
----关闭或禁用服务器审核规范
ALTER SERVER AUDIT SPECIFICATION [Server_AuditSpecification]
WITH (STATE = OFF)
---启用服务器审核对象
ALTER SERVER AUDIT [my_first_audit] WITH (STATE = ON)
---关闭或禁用服务器审核对象
ALTER SERVER AUDIT [my_first_audit] WITH (STATE = OFF)
/*创建数据库审核规范*/
USE [db_test]
GO
CREATE DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
FOR SERVER AUDIT [my_first_audit]
ADD (SELECT ON OBJECT::[dbo].[a] BY [audit])---制定审核操作类型、对象类（object、schema、database）、对象架构（DBO)）对象名称（a）、 主体名称（登录名或用户名）
---具体审核操作类型：见联机丛书
GO
---启用数据库审核规范
ALTER DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
WITH (STATE = ON)
---关闭或禁用数据库审核规范
ALTER DATABASE AUDIT SPECIFICATION [Database_AuditSpecification]
WITH (STATE = OFF)