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 |
Very nicely done. Thank you. I inherited a long list of Audit ID ints and had no clue what they are.
ReplyDelete