SQL Sever 2008 introduces auditing feature which can audit both server-level events and database-level events and several specific database actions. Please check http://msdn.microsoft.com/en-us/library/cc280386.aspx for more details.
One difficulty the user may have is which action group should be used when trying to audit the interested events. For example if the user wants to audit all of the “create login” actions, first it should be a server audit specification to be created because “create login” is a server-level event, and now we need to decide which action group should be added to this server audit specification. The DMV sys.dm_audit_actions can help the user to find such information, for the above example we can do the following query:
select * from sys.dm_audit_actions where name='create' and class_desc='login'
And the returned result is:
The value of the containing_group_name is the action group you should add to the server audit specification for auditing “create login”.
Another example is that if want to audit “alter shema” events, then do the following query:
select * from sys.dm_audit_actions where name='alter' and class_desc='schema'
will tell you the event to audit is DATABASE_OBJECT_CHANGE_GROUP.
One thing to be noted is that for all schema-scoped objects, such as table, function, procedure etc., they are all deemed as object and the audit action group for create/alter/drop these object is SCHEMA_OBJECT_CHANGE_GROUP, the query to the DMV is:
select * from sys.dm_audit_actions where name='alter' and class_desc='object'
I was wondering if you could help, even though it is not related to SQL2008, but rather trace events in SQL2005 profiler...
when is the "Audit Schema Object Access" Event Class fired when object type is stored procedures, tables, view?
Is it every single time the object is used?
I'm trying to find a way of identifying unused objects.
Audit Schema Object Access Event Class is fired everytime a permission is checked on a schema object (SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.). Permissions are checked everytime an object is accessed in a query, even when a query plan is reused.
that's great news for me! thx for your quick response.