此文章主要是脚本示例，更多说明看官方文档：审核（数据库引擎）
–必须在 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
福报够的人，从来就没听到过是非。
