Enable and configure auditing in SQL Server 2008 R2

2 11 2013

Since I mentioned the auditing feature in my prior post I decided to post a quick script to setup and start database auditing in SQL Server 2008 R2.

/* 
Created By: Christopher Provolt
Created: August 2, 2013
Description: Create and intialize database auditing (SQL Server 2008 R2)

**** NOTES ****
Replace ServerName With the name of the server if it is a named instance also include the instance name i.e. ServerName-InstanceName-Audit

Replace Server Audit Name with the name generated for the intial Server Audit object

Make sure to set appropriate file paths to match your environment  */

USE [master] GO

/* Create the initial Server Audit Object */ 
CREATE SERVER AUDIT [ServerName-Audit] TO FILE(FILEPATH = 'E:\Audit\', MAXSIZE=50MB, MAX_ROLLOVER_FILES=200) WITH (QUEUE_DELAY = 1000, ON_FAILURE=CONTINUE) GO

/* Create the Server Audit Specification */  
CREATE SERVER AUDIT SPECIFICATION [ServerName-ServerAudit_Specification] FOR SERVER AUDIT [ServerName-Audit] ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP), ADD (AUDIT_CHANGE_GROUP), ADD (BACKUP_RESTORE_GROUP), ADD (DATABASE_CHANGE_GROUP), ADD (DATABASE_OBJECT_ACCESS_GROUP), ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP), ADD (DATABASE_OPERATION_GROUP), ADD (DATABASE_OWNERSHIP_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP), ADD (FAILED_LOGIN_GROUP), ADD (LOGIN_CHANGE_PASSWORD_GROUP), ADD (LOGOUT_GROUP), ADD (SCHEMA_OBJECT_ACCESS_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP), ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OBJECT_CHANGE_GROUP), ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP), ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP), ADD (SERVER_OPERATION_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_CHANGE_GROUP), ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (SERVER_STATE_CHANGE_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP), ADD (TRACE_CHANGE_GROUP) GO

/* Enable the Server Audit */ 
ALTER SERVER AUDIT [ServerName-Audit] WITH (STATE = ON)  GO   

 /* Enable the Server Specification Audit */  
ALTER SERVER AUDIT SPECIFICATION [ServerName-ServerAudit_Specification]  WITH (STATE = ON)  GO




SQL Server Audit action_id List

2 08 2013

While reviewing some audit logs genereated in SQL Server 2008 auditing I came across a a few action_id values that were not completely obvious to me. I began the great google search in hopes of finding a table that mapped out this information and was somewhat unsuccessful. What I did manage to put together was a a query to determine what they all meant.

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

The above query will list all the action_id’s and what they actually mean in the world of SQL.  In order to save time for everyone the trimmed down list is below.

action_id name
ACDO DATABASE_OBJECT_ACCESS_GROUP
ACO SCHEMA_OBJECT_ACCESS_GROUP
ADBO BULK ADMIN
ADDP DATABASE_ROLE_MEMBER_CHANGE_GROUP
ADSP SERVER_ROLE_MEMBER_CHANGE_GROUP
AL ALTER
ALCN ALTER CONNECTION
ALRS ALTER RESOURCES
ALSS ALTER SERVER STATE
ALST ALTER SETTINGS
ALTR ALTER TRACE
APRL ADD MEMBER
AS ACCESS
AUSC AUDIT SESSION CHANGED
AUSF AUDIT SHUTDOWN ON FAILURE
AUTH AUTHENTICATE
BA BACKUP
BAL BACKUP LOG
BRDB BACKUP_RESTORE_GROUP
C2OF TRACE AUDIT C2OFF
C2ON TRACE AUDIT C2ON
CCLG CHANGE LOGIN CREDENTIAL
CMLG CREDENTIAL MAP TO LOGIN
CNAU AUDIT_CHANGE_GROUP
CO CONNECT
CP CHECKPOINT
CR CREATE
D DENY
DBCC DBCC
DBCG DBCC_GROUP
DL DELETE
DPRL DROP MEMBER
DR DROP
DWC DENY WITH CASCADE
EX EXECUTE
FT FULLTEXT
FTG FULLTEXT_GROUP
G GRANT
GRDB DATABASE_PERMISSION_CHANGE_GROUP
GRDO DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
GRO SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
GRSO SERVER_OBJECT_PERMISSION_CHANGE_GROUP
GRSV SERVER_PERMISSION_CHANGE_GROUP
GWG GRANT WITH GRANT
IMDP DATABASE_PRINCIPAL_IMPERSONATION_GROUP
IMP IMPERSONATE
IMSP SERVER_PRINCIPAL_IMPERSONATION_GROUP
IN INSERT
LGB BROKER LOGIN
LGBG BROKER_LOGIN_GROUP
LGDA DISABLE
LGDB CHANGE DEFAULT DATABASE
LGEA ENABLE
LGFL FAILED_LOGIN_GROUP
LGIF LOGIN FAILED
LGIS LOGIN SUCCEEDED
LGLG CHANGE DEFAULT LANGUAGE
LGM DATABASE MIRRORING LOGIN
LGMG DATABASE_MIRRORING_LOGIN_GROUP
LGNM NAME CHANGE
LGO LOGOUT
LGSD SUCCESSFUL_LOGIN_GROUP
LO LOGOUT_GROUP
MNDB DATABASE_CHANGE_GROUP
MNDO DATABASE_OBJECT_CHANGE_GROUP
MNDP DATABASE_PRINCIPAL_CHANGE_GROUP
MNO SCHEMA_OBJECT_CHANGE_GROUP
MNSO SERVER_OBJECT_CHANGE_GROUP
MNSP SERVER_PRINCIPAL_CHANGE_GROUP
NMLG NO CREDENTIAL MAP TO LOGIN
OP OPEN
OPDB DATABASE_OPERATION_GROUP
OPSV SERVER_OPERATION_GROUP
PWAR APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
PWC CHANGE PASSWORD
PWCG LOGIN_CHANGE_PASSWORD_GROUP
PWCS CHANGE OWN PASSWORD
PWEX PASSWORD EXPIRATION
PWMC MUST CHANGE PASSWORD
PWPL PASSWORD POLICY
PWR RESET PASSWORD
PWRS RESET OWN PASSWORD
PWU UNLOCK ACCOUNT
R REVOKE
RC RECEIVE
RF REFERENCES
RS RESTORE
RWC REVOKE WITH CASCADE
RWG REVOKE WITH GRANT
SL SELECT
SN SEND
SPLN SHOW PLAN
STSV SERVER_STATE_CHANGE_GROUP
SUQN SUBSCRIBE QUERY NOTIFICATION
SVCN SERVER CONTINUE
SVPD SERVER PAUSED
SVSD SERVER SHUTDOWN
SVSR SERVER STARTED
TASA TRACE AUDIT START
TASP TRACE AUDIT STOP
TO TAKE OWNERSHIP
TODB DATABASE_OWNERSHIP_CHANGE_GROUP
TODO DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
TOO SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
TOSO SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
TRCG TRACE_CHANGE_GROUP
TRO TRANSFER
UP UPDATE
USAF CHANGE USERS LOGIN AUTO
USLG CHANGE USERS LOGIN
VDST VIEW DATABASE STATE
VSST VIEW SERVER STATE
VWCT VIEW CHANGETRACKING
XA EXTERNAL ACCESS ASSEMBLY
XU UNSAFE ASSEMBLY