Mapping Server Audit Action IDs

Solomon Rutzky tries to reverse engineer the action_id values used in SQL Server audits:

This post is, for the most part, a continuation of Server Audit Mystery 1: Filtering class_type gets Error Msg 25713. In that post I was trying to filter on the class_type field / predicate source (i.e. the object, or whatever, that the event is on). The tricky part was that class_type is supposed to be filterable on string values such as U for “User Table”, P for “Stored Procedure”, etc. But, the reality is that it has to be filtered using numbers, and that list does not seem to be documented anywhere. Fortunately, I was able to find the list of possible numbers, so all is well with regards to class_type.

When it comes to the action_id field, however, we are not so lucky. It, too, should be filterable on the string codes that are returned by sys.database_audit_specification_details and sys.fn_get_audit_file. But, just like class_type, only numbers can be used for filtering. And, like class_type, that list of numbers does not seem to be documented anywhere. But, unlike class_type, I cannot find any list of numbers (in SQL Server) that corresponds to the available actions. I did find a list of statements by looking at Extended Events info (since Audits use Extended Events):

FROM sys.dm_xe_map_values
WHERE [name] = N'statement_type';

but it wasn’t the correct list. So, this means that I have to go hunting for these values the even-harder way

Read on for some fun with binary tree search.

Related Posts

Preventing Server Manager From Loading

Steve Stedman shows how to prevent the Server Manager app from loading whenever you RDP into a Windows Server machine: If you frequently connect to many different SQL Server as I do, you are probably used to the Server Manager loading slowly when you log in with Remote Desktop. The Server Manager has a bad […]

Read More

Listing SQL Server Instances On A Server

Lori Brown has a script which lists installed SQL Server instances: It is pretty common that I frequently am asked to take over a server with SQL on it sight unseen. Many times shops many not be fully aware if additional instances are or have been installed on their server. Since I am working on […]

Read More


January 2018
« Dec Feb »