Server Audit Examples in SQL Server 2008

From SQLServerPedia

1 Introduction
2 Server Audit in SQL Server 2008
3 What's in Server Audit?
4 Server Audit Examples
5 How to read Audit data
6 Related Reading About Server Audit
7 Author Credits

Introduction

Prior to SQL Server 2008, auditing in SQL Server was done using a variety of tools and options especially looking at the application logs in windows and SQL Server error logs, using a combination of DDL Triggers like server and logon triggers and using SQL Trace. This makes centralizing auditing of SQL Server very tedious and complex task. Also, it requires significant technical knowledge for auditors to complete these tasks. This is a primer, 101 article for SQL Server Audit and shouldn't be considered as comprehensive work.

Server Audit in SQL Server 2008

SQL Server 2008 introduced new auditing capabilities to help centralize all auditing of the SQL Server instance. This new feature has many benefits compared to previous versions as the auditing capabilities are embedded into the Database Engine. Server Audit is built using the infrastructure built for Extended events, a high performance event monitoring mechanism. SQL ServerAudit has very little to no performance impact compared to previous editions.

What's in Server Audit?

There are 4 key components in SQL Server Audit. Server Audit, Server Audit Specification, Database Audit Specification and Target.

Server Audit: A Server Audit is the base component and is a collection of server or database level actions. Server Audit can be created and modified with the following commands.

USE master
GO
CREATE SERVER AUDIT [Password_Audit] 
TO FILE  
(  FILEPATH = N'C:\' 
   ,MAXSIZE = 10 MB 
   ,MAX_ROLLOVER_FILES = 30 
   ,RESERVE_DISK_SPACE = OFF 
) 
WITH 
(  QUEUE_DELAY = 1000 
   ,ON_FAILURE = CONTINUE 
) 
GO

ALTER SERVER AUDIT [Password_Audit] 
WITH (STATE = ON) 
GO

DROP SERVER AUDIT [Password_Audit] 
GO

ALTER ANY SERVER AUDIT permission, part of CONTROL SERVER is required to use the above commands.

Server Audit Specification: A Server Audit Specification is used to monitor several instance level audit actions and will be a part of the SERVER AUDIT. Server Audit Specification can be created and modified with the following commands.

CREATE SERVER AUDIT SPECIFICATION Password_Change_Specification 
FOR SERVER AUDIT [Password_Audit] 
    ADD (LOGIN_CHANGE_PASSWORD_GROUP); 
GO

ALTER SERVER AUDIT SPECIFICATION Password_Change_Specification 
WITH (STATE = ON) 
GO

DROP SERVER AUDIT SPECIFICATION Password_Change_Specification 
GO

ALTER ANY SERVER AUDIT permission, part of CONTROL SERVER is required to use the above commands.

Database Audit Specification: A Database Audit Specification is used to monitor several database level audit actions and will be a part of the SERVER AUDIT. This allows to audit at a more granular level. Database Audit Specification can be created and modified with the following commands.

USE AdventureWorks2008
GO
CREATE DATABASE AUDIT SPECIFICATION Read_Sensitive_Data_Specification 
FOR SERVER AUDIT [ReadSensitive_Audit] 
ADD (SELECT ON HumanResources.EmployeePayHistory BY dbo)
); 
GO

ALTER DATABASE AUDIT SPECIFICATION Read_Sensitive_Data_Specification 
WITH (STATE = ON) 
GO

DROP DATABASE AUDIT SPECIFICATION Read_Sensitive_Data_Specification
GO

ALTER DATABASE SERVER SPECIFICATION permission, part of CONTROL DATABASE is required to use the above commands.

Target: The audit data can be routed to a file, windows security event log or windows application event log.