sqlserver 2008 R2 审计

/*

审计web账户对jr数据库的rate表操作
*/

--步骤1：创建审核对象

USE master
GO
CREATE SERVER AUDIT rate
TO FILE (FILEPATH='s:\audit\table\',MAXSIZE=100 MB)
WITH (QUEUE_DELAY = 3000)
go

--步骤2：创建数据库审核规范，并将其映射到审核对象。

USE jr
GO
create DATABASE AUDIT SPECIFICATION audit_table_rate
FOR SERVER AUDIT rate
ADD (UPDATE
ON jr.dbo.rate BY test@kk.com)
WITH (STATE=off)
go --审计的是jr库rate表

--步骤3：查看状态
SELECT is_state_enabled,*
FROM master.sys.server_file_audits

SELECT is_state_enabled,*
FROM jinri.sys.database_audit_specifications

--步骤4：开启
use master
ALTER SERVER AUDIT rate WITH (STATE=On)
go

USE jr
ALTER DATABASE AUDIT SPECIFICATION audit_table_rate WITH (STATE=on)
go

--步骤5：查看审计结果

SELECT session_server_principal_name, statement, event_time,action_id
FROM fn_get_audit_file ('s:\audit\table\rate*',NULL, NULL)
order by event_time desc
go

--步骤5：查看审计结果

SELECT * FROM (
SELECT session_server_principal_name, statement, dateadd (hh,8,event_time) as event_time,action_id
FROM fn_get_audit_file ('s:\audit\table\rate*',NULL, NULL)
--order by event_time DESC
) c
WHERE c.statement LIKE '%set lock=1%'
order by event_time DESC
go

SELECT session_server_principal_name, statement, event_time,action_id
FROM fn_get_audit_file ('s:\audit\table\rate*',NULL, NULL)
order by event_time DESC