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):
| Action_ID_Int | action_id | NAME |
| 1329873729 | ACDO | DATABASE_OBJECT_ACCESS_GROUP |
| 542065473 | ACO | SCHEMA_OBJECT_ACCESS_GROUP |
| 1329742913 | ADBO | BULK ADMIN |
| 1346651201 | ADDP | DATABASE_ROLE_MEMBER_CHANGE_GROUP |
| 1347634241 | ADSP | SERVER_ROLE_MEMBER_CHANGE_GROUP |
| 538987585 | AL | ALTER |
| 1313033281 | ALCN | ALTER CONNECTION |
| 1397902401 | ALRS | ALTER RESOURCES |
| 1397967937 | ALSS | ALTER SERVER STATE |
| 1414745153 | ALST | ALTER SETTINGS |
| 1381256257 | ALTR | ALTER TRACE |
| 1280462913 | APRL | ADD MEMBER |
| 538989377 | AS | ACCESS |
| 1129534785 | AUSC | AUDIT SESSION CHANGED |
| 1179866433 | AUSF | AUDIT SHUTDOWN ON FAILURE |
| 1213486401 | AUTH | AUTHENTICATE |
| 538984770 | BA | BACKUP |
| 541868354 | BAL | BACKUP LOG |
| 1111773762 | BRDB | BACKUP_RESTORE_GROUP |
| 1179595331 | C2OF | TRACE AUDIT C2OFF |
| 1313813059 | C2ON | TRACE AUDIT C2ON |
| 1196180291 | CCLG | CHANGE LOGIN CREDENTIAL |
| 1196182851 | CMLG | CREDENTIAL MAP TO LOGIN |
| 1430343235 | CNAU | AUDIT_CHANGE_GROUP |
| 538988355 | CO | CONNECT |
| 538988611 | CP | CHECKPOINT |
| 538989123 | CR | CREATE |
| 538976324 | D | DENY |
| 1179074884 | DAGF | FAILED_DATABASE_AUTHENTICATION_GROUP |
| 1279738180 | DAGL | DATABASE_LOGOUT_GROUP |
| 1397178692 | DAGS | SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP |
| 1178681924 | DBAF | DATABASE AUTHENTICATION FAILED |
| 1396785732 | DBAS | DATABASE AUTHENTICATION SUCCEEDED |
| 1128481348 | DBCC | DBCC |
| 1195590212 | DBCG | DBCC_GROUP |
| 541868612 | DBL | DATABASE LOGOUT |
| 538987588 | DL | DELETE |
| 1280462916 | DPRL | DROP MEMBER |
| 538989124 | DR | DROP |
| 541284164 | DWC | DENY WITH CASCADE |
| 538990661 | EX | EXECUTE |
| 538989638 | FT | FULLTEXT |
| 541545542 | FTG | FULLTEXT_GROUP |
| 538976327 | G | GRANT |
| 1111773767 | GRDB | DATABASE_PERMISSION_CHANGE_GROUP |
| 1329877575 | GRDO | DATABASE_OBJECT_PERMISSION_CHANGE_GROUP |
| 542069319 | GRO | SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP |
| 1330860615 | GRSO | SERVER_OBJECT_PERMISSION_CHANGE_GROUP |
| 1448301127 | GRSV | SERVER_PERMISSION_CHANGE_GROUP |
| 541546311 | GWG | GRANT WITH GRANT |
| 1346653513 | IMDP | DATABASE_PRINCIPAL_IMPERSONATION_GROUP |
| 542133577 | IMP | IMPERSONATE |
| 1347636553 | IMSP | SERVER_PRINCIPAL_IMPERSONATION_GROUP |
| 538988105 | IN | INSERT |
| 541214540 | LGB | BROKER LOGIN |
| 1195525964 | LGBG | BROKER_LOGIN_GROUP |
| 1094993740 | LGDA | DISABLE |
| 1111770956 | LGDB | CHANGE DEFAULT DATABASE |
| 1095059276 | LGEA | ENABLE |
| 1279674188 | LGFL | FAILED_LOGIN_GROUP |
| 1179207500 | LGIF | LOGIN FAILED |
| 1397311308 | LGIS | LOGIN SUCCEEDED |
| 1196181324 | LGLG | CHANGE DEFAULT LANGUAGE |
| 541935436 | LGM | DATABASE MIRRORING LOGIN |
| 1196246860 | LGMG | DATABASE_MIRRORING_LOGIN_GROUP |
| 1296975692 | LGNM | NAME CHANGE |
| 542066508 | LGO | LOGOUT |
| 1146308428 | LGSD | SUCCESSFUL_LOGIN_GROUP |
| 538988364 | LO | LOGOUT_GROUP |
| 1111772749 | MNDB | DATABASE_CHANGE_GROUP |
| 1329876557 | MNDO | DATABASE_OBJECT_CHANGE_GROUP |
| 1346653773 | MNDP | DATABASE_PRINCIPAL_CHANGE_GROUP |
| 542068301 | MNO | SCHEMA_OBJECT_CHANGE_GROUP |
| 1330859597 | MNSO | SERVER_OBJECT_CHANGE_GROUP |
| 1347636813 | MNSP | SERVER_PRINCIPAL_CHANGE_GROUP |
| 1196182862 | NMLG | NO CREDENTIAL MAP TO LOGIN |
| 538988623 | OP | OPEN |
| 1111773263 | OPDB | DATABASE_OPERATION_GROUP |
| 1448300623 | OPSV | SERVER_OPERATION_GROUP |
| 1380013904 | PWAR | APPLICATION_ROLE_CHANGE_PASSWORD_GROUP |
| 541284176 | PWC | CHANGE PASSWORD |
| 1195595600 | PWCG | LOGIN_CHANGE_PASSWORD_GROUP |
| 1396922192 | PWCS | CHANGE OWN PASSWORD |
| 1480939344 | PWEX | PASSWORD EXPIRATION |
| 1129142096 | PWMC | MUST CHANGE PASSWORD |
| 1280333648 | PWPL | PASSWORD POLICY |
| 542267216 | PWR | RESET PASSWORD |
| 1397905232 | PWRS | RESET OWN PASSWORD |
| 542463824 | PWU | UNLOCK ACCOUNT |
| 538976338 | R | REVOKE |
| 538985298 | RC | RECEIVE |
| 538986066 | RF | REFERENCES |
| 538989394 | RS | RESTORE |
| 541284178 | RWC | REVOKE WITH CASCADE |
| 541546322 | RWG | REVOKE WITH GRANT |
| 538987603 | SL | SELECT |
| 538988115 | SN | SEND |
| 1313624147 | SPLN | SHOW PLAN |
| 1448301651 | STSV | SERVER_STATE_CHANGE_GROUP |
| 1313953107 | SUQN | SUBSCRIBE QUERY NOTIFICATION |
| 1313035859 | SVCN | SERVER CONTINUE |
| 1146115667 | SVPD | SERVER PAUSED |
| 1146312275 | SVSD | SERVER SHUTDOWN |
| 1381193299 | SVSR | SERVER STARTED |
| 1095975252 | TASA | TRACE AUDIT START |
| 1347633492 | TASP | TRACE AUDIT STOP |
| 538988372 | TO | TAKE OWNERSHIP |
| 1111773012 | TODB | DATABASE_OWNERSHIP_CHANGE_GROUP |
| 1329876820 | TODO | DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP |
| 542068564 | TOO | SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP |
| 1330859860 | TOSO | SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP |
| 1195594324 | TRCG | TRACE_CHANGE_GROUP |
| 542069332 | TRO | TRANSFER |
| 1346847573 | UCGP | USER_CHANGE_PASSWORD_GROUP |
| 1195459669 | UDAG | USER_DEFINED_AUDIT_GROUP |
| 1430340693 | UDAU | USER DEFINED AUDIT |
| 538988629 | UP | UPDATE |
| 1178686293 | USAF | CHANGE USERS LOGIN AUTO |
| 1196184405 | USLG | CHANGE USERS LOGIN |
| 1129599829 | USTC | COPY PASSWORD |
| 1414743126 | VDST | VIEW DATABASE STATE |
| 1414746966 | VSST | VIEW SERVER STATE |
| 1413699414 | VWCT | VIEW CHANGETRACKING |
| 538984792 | XA | EXTERNAL ACCESS ASSEMBLY |
| 538989912 | XU | UNSAFE ASSEMBLY |
