SqlServer 开启审计功能

SqlServer脚本开启审计功能 记录数据库操作（如果数据量大，MAXSIZE调大）

--第一步  换到master数据库
CREATE SERVER AUDIT [Audit-log]
TO FILE 
( FILEPATH = N'D:\db_audit\'
 ,MAXSIZE = 5 GB
 ,MAX_ROLLOVER_FILES= 5
 ,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 60000
 ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit-log] WITH (STATE = ON)
GO



--第二步

CREATE SERVER AUDIT SPECIFICATION [Server-audit]
FOR SERVER AUDIT [Audit-log]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (DATABASE_LOGOUT_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (DATABASE_OPERATION_GROUP)
WITH (STATE = ON)
GO

--第三步   换到要审计的数据库
CREATE DATABASE AUDIT SPECIFICATION [Db-audit]
FOR SERVER AUDIT [Audit-log]
ADD (DELETE,UPDATE ON DATABASE::[数据库名] BY [public])
WITH (STATE = ON)
GO

--第四步
select  
DATEADD(mi,DATEDIFF(mi,GETUTCDATE(),GETDATE()),event_time) AS event_time
, f.statement  
, f.succeeded
, f.file_name  
, database_name
, object_name   
, f.server_instance_name 
from sys.fn_get_audit_file('D:\db_audit\生成的文件名',default,default) f
where database_name = '数据库名'
order by f.event_time desc