SQL Server Audits and Action_IDs

We were recently re-doing our SQL Server Audits and I was reminded again how painful setting the filters can be. MS expects an integer for “action_id”, but to actually use, them you need to know what those actually mean.

I came across this blog post by Chris Provolt listing the text versions of the action_id’s. That was helpful, especially the quick query to see what’s available:

Select DISTINCT action_id,name,class_desc,parent_class_desc from sys.dm_audit_actions

However, as you can tell by running this, the action_id’s returned are all text values. That doesn’t help when trying to set up your SQL Audits.

MSDN provides code for a function to translate the text audit_id into the expected numeric value.

CREATE FUNCTION dbo.Getint_action_id (@action_id VARCHAR(4))
returns INT
BEGIN
DECLARE @x INT;

SET @x = CONVERT(INT, CONVERT(VARBINARY(1), Upper(
Substring(@action_id, 1, 1))));

IF Len(@action_id) >= 2
SET @x = CONVERT(INT, CONVERT(VARBINARY(1), Upper(
Substring(@action_id, 2, 1))
))
* Power(
2, 8) + @x;
ELSE
SET @x = CONVERT(INT, CONVERT(VARBINARY(1), ‘ ‘)) * Power(2, 8) + @x;

IF Len(@action_id) >= 3
SET @x = CONVERT(INT, CONVERT(VARBINARY(1), Upper(
Substring(@action_id, 3, 1))
))
* Power(
2, 16) + @x;
ELSE
SET @x = CONVERT(INT, CONVERT(VARBINARY(1), ‘ ‘)) * Power(2, 16) + @x;

IF Len(@action_id) >= 4
SET @x = CONVERT(INT, CONVERT(VARBINARY(1), Upper(
Substring(@action_id, 4, 1))
))
* Power(
2, 24) + @x;
ELSE
SET @x = CONVERT(INT, CONVERT(VARBINARY(1), ‘ ‘)) * Power(2, 24) + @x;

RETURN @x;
END; 

Once you create that function, you can use it to generate a list of the Integer action_ids, the text action_ids, and the name/description of those action_ids. (You can also expand it out to see which actions are applicable to various objects at the server and database level if you so desire.)

SELECT DISTINCT dbo.Getint_action_id(action_id) Action_ID_Int,
action_id, NAME AS Action_Description
–,class_desc,parent_class_desc
FROM   sys.dm_audit_actions
ORDER  BY action_id

This will result in the following values (as of SQL 2012):