Share via

How To Choose Audit Action Group When Using Auditing in SQL Server 2008

  • Article

SQL Sever 2008 introduces auditing feature which can audit both server-level events and database-level events and several specific database actions. Please check https://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:

action_id

name

class_desc

covering_action_name

parent_class_desc

CR

CREATE

LOGIN

NULL

SERVER

 

covering_parent_action_name

configuration_level

SERVER_PRINCIPAL_CHANGE_GROUP

NULL

 

containing_group_name

action_in_log

SERVER_PRINCIPAL_CHANGE_GROUP

1

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'

Comments

  • Anonymous
    February 03, 2010
    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. thx jt

  • Anonymous
    February 03, 2010
    The comment has been removed

  • Anonymous
    February 03, 2010
    that's great news for me! thx for your quick response.

  • Anonymous
    January 07, 2015
    Excellent document and help me to identify the right action group required for auditing