How to create a simple database-level audit using SQL Server Audit

How do you implement "Auditing" with your SQL Server database? Do you know that SQL Server Audit is the best and simplest way of implementing "Auditing" for your database? Unfortunately, we still see oldest, traditional implementations for auditing and "Trigger" is the most common way of implementing it though it does not support on SELECT queries. In addition to that custom code implementation is common too.

Although SQL Server Audit was introduced with SQL Server 2008, as I see, it is still not popular among database administrators (or more precisely, it is an unknown item for them). Auditing is required for many reasons, specially on security, hence actions performed by users regardless of the type of the action should be captured and logged. This post speaks about "SQL Server Audit" and how to implement it for capturing actions performed by users using TSQL though it can be easily implemented using GUIs given.

SQL Server Audit is the main auditing tool available with SQL Server. In addition to this, SQL Server offers few more ways for auditing such as C2, Common Criteria Compliance, Trigger, SQL Server Profiler and SQL Trace. However, they do not offer much facilities as SQL Server Audit does.

SQL Server Audit support both server-level and database-level auditing. All editions support server-level auditing bu only Enterprise, Developer and Evaluation editions support database-level auditing.

SQL Server Audit is based on Extended Events which is a lightweight eventing engine that has very little impact on the database being monitored. Extended events feature allows you to define an action for specific event. When SQL Server executes an internal code related to the event specified, it checks and sees whether an action has been set for it, if available, it fires and send details to the target. For more info on Extended Events, see this: https://msdn.microsoft.com/en-us/library/bb630282.aspx

Let's see how to create a simple audit using TSQL. In order to create an audit, following steps have to be performed;

1. Create an Audit with name and target. Additional options such as ON-FAILURE can be set with this step too.
2. Add Audit Specification for the created Audit. Either server or database specification can added.
3. Add Actions or Action Groups to the created Audit Specification.
4. Enable all added items.

That is all. Here is the code for implementing auditing on Production schema of the AdventureWorks database for SELECT action.

view plainprint?

1. -- Create a SQL Server Audit and define its   
2. -- target as the windows application log  
3.   
4. USE master;  
5. GO  
6.   
7. CREATE SERVER AUDIT AdventureWorksLog  
8.  TO APPLICATION_LOG  
9.  WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);  
10. GO  
11.   
12.   
13. -- Create database audit specification  
14. -- for Production table in AdventureWorks  
15.   
16. USE AdventureWorks2014;  
17. GO  
18.   
19. CREATE DATABASE AUDIT SPECIFICATION ProductionSpecification  
20.  FOR SERVER AUDIT AdventureWorksLog  
21.  ADD (SELECT ON SCHEMA::Production BY PUBLIC);  
22. GO  
23.   
24. -- Query the sys.server_audits system view  
25. -- Note the is_state_enabled  
26.   
27. SELECT * FROM sys.server_audits;  
28. GO  
29.   
30. -- Enable the server audit and   
31. -- AdventureWorks database audit specification  
32.   
33. USE master;  
34. GO  
35.   
36. ALTER SERVER AUDIT AdventureWorksLog WITH (STATE = ON);  
37. GO  
38.   
39. USE AdventureWorks2014;  
40. GO  
41.    
42. ALTER DATABASE AUDIT SPECIFICATION ProductionSpecification  
43.  WITH (STATE = ON);  
44. GO  
45.   
46. -- Generate an auditable event by querying a table  
47. -- in the Production schema. Also execute a query  
48. -- that should not be audited  
49.   
50. SELECT * FROM Production.Product;  
51. GO  
52. SELECT * FROM Sales.Currency;  
53. GO  

-- Create a SQL Server Audit and define its 
-- target as the windows application log

USE master;
GO

CREATE SERVER AUDIT AdventureWorksLog
 TO APPLICATION_LOG
 WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO


-- Create database audit specification
-- for Production table in AdventureWorks

USE AdventureWorks2014;
GO

CREATE DATABASE AUDIT SPECIFICATION ProductionSpecification
 FOR SERVER AUDIT AdventureWorksLog
 ADD (SELECT ON SCHEMA::Production BY PUBLIC);
GO

-- Query the sys.server_audits system view
-- Note the is_state_enabled

SELECT * FROM sys.server_audits;
GO

-- Enable the server audit and 
-- AdventureWorks database audit specification

USE master;
GO

ALTER SERVER AUDIT AdventureWorksLog WITH (STATE = ON);
GO

USE AdventureWorks2014;
GO
 
ALTER DATABASE AUDIT SPECIFICATION ProductionSpecification
 WITH (STATE = ON);
GO

-- Generate an auditable event by querying a table
-- in the Production schema. Also execute a query
-- that should not be audited

SELECT * FROM Production.Product;
GO
SELECT * FROM Sales.Currency;
GO

Once the SELECT statements are executed, let's have a look on Event Viewer and see. You should see the event related to the first statement logged.

For more info on SQL Server Audit, refer here: https://technet.microsoft.com/en-us/library/cc280505(v=sql.105).aspx