SQL Server审核/审计

create server audit myAudit
to file
 (
  filepath = 'D:\',         --审核日志的文件路径
  maxsize  = 500MB,         --审核文件可以增大到的最大大小
  max_rollover_files = 10,  --保留在文件系统中，外加当前文件的最大文件数
  reserve_disk_space = off  --按MAXSIZE值为磁盘上的文件预先分配大小
 )
with 
 (
  queue_delay = 1000,     --确定在强制处理审核操作之前，可以延迟的毫秒数
  on_failure = continue   --当不能写入目标时，sql实例是否关闭
 )

--注：查看服务器审记规范可审记的活动
Select name from sys.dm_audit_actions where class_desc='SERVER' and configuration_level='Group'

create server audit specification mySevSpf
for server audit myAudit
add(BACKUP_RESTORE_GROUP),
add(DATABASE_CHANGE_GROUP)

--注：查看，数据库审记规范可审记的活动
Select name from sys.dm_audit_actions where class_desc='DATABASE' and configuration_level='Group'

create database audit specification myDtbSpf
for server audit myAudit
add (select on dbo.stu by public),
add (update on dbo.stu by public)

alter server audit specification mySevSpf with (state=on)
alter server audit myAudit with (state=on)
use xscj
go
alter database audit specification myDtbSpf with (state=on)
--禁用审核同理置为off即可

select event_time, action_id, succeeded , session_id ,session_server_principal_name ,
object_name , statement , file_name, audit_file_offset
from sys.fn_get_audit_file('D:\',default,default)

drop server audit myAudit
drop server audit specification mySevSpf
use xscj
go
drop database audit specification myDtbSpf