create audit例子

--setup
USE MASTER
GO
-- Create the server audit
CREATE SERVER AUDIT test_audit
    TO FILE ( FILEPATH = 
'E:\audit' );
GO
-- Enable the server audit
ALTER SERVER AUDIT test_audit 
WITH (STATE = ON) ;
GO
-- Move to the target database
USE Fortest_lmtopo
GO
CREATE DATABASE AUDIT SPECIFICATION Audit_MEETING_VISITOR
FOR SERVER AUDIT test_audit
ADD (update , INSERT,delete
     ON lmuser.MEETING_VISITOR by public)--audit these operation executed by any user because all users are inhert from 'public'role
WITH (STATE = ON)
GO
--CREATE DATABASE AUDIT SPECIFICATION Audit_update_Fortest_lmtopo
--FOR SERVER AUDIT test_audit
--ADD (DATABASE_OBJECT_ACCESS_GROUP
--)
--WITH (STATE = ON)


--query
select a.name as [Action],c.class_type_desc as [ObjectType],f.schema_name,f.object_name,f.statement from
sys.fn_get_audit_file('c:\case\*',default,default) f inner join sys.dm_audit_actions a
on f.action_id=a.action_id inner join sys.dm_audit_class_type_map c on f.class_type=c.class_type
AND c.securable_class_desc = a.class_desc


GO
--clean
go
use Fortest_lmtopo
go
ALTER database AUDIT specification Audit_MEETING_VISITOR with(state=off) 
go
drop database audit specification Audit_MEETING_VISITOR
go
use master
go
alter server audit test_audit with (state=off)
go
drop server audit test_audit
go

http://technet.microsoft.com/en-us/library/cc280404.aspx

http://www.sqldbatips.com/showarticle.asp?ID=136

 USE MASTER
GO
-- Create the server audit
CREATE SERVER AUDIT test_audit
TO FILE ( FILEPATH = 
'C:\cases\Cluster\116020613677365- SQL server not starting up in cluster' );
GO
-- Enable the server audit
ALTER SERVER AUDIT test_audit 
WITH (STATE = ON) ;
GO


CREATE SERVER AUDIT SPECIFICATION audit_specification_name
FOR SERVER AUDIT test_audit
ADD ( SERVER_PERMISSION_CHANGE_GROUP ) ,
add (SERVER_PRINCIPAL_CHANGE_GROUP)
 WITH ( STATE =  ON )