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