Systems Engineering and RDBMS

SQL Server Audit in SQL Server 2008

Posted by decipherinfosys on May 22, 2008

SQL Server 2005 already has a good set of auditing capabilities namely C2 Audit Mode Option, DDL Triggers and SQL Server Profiler (besides writing one’s own DML triggers). In SQL Server 2008, one can make use of CDC (Change Data Capture) for capturing the changes as well as a new feature called SQL Server Audit.

SQL Server Audit is built at the top of XEVENT which stands for Extended Events which is a new event infrastructure that has been introduced in SQL Server 2008.

SQL Server Audit involves these steps:

a) We first need to create a SQL Server Audit object which is used to collect the instance or database level actions. The results of the audit are sent to a target which can be a file, windows application event log or security event log and can be viewed via the event viewer.

b) We need to create a database audit specification or a server (for an instance) audit specification which needs to be mapped to the audit object created in Step #(a). This specification collects the pre-defined groups of atomic events and sends them to the Audit Object and that records them in the target (file, application or security event logs).

c) Then we need to enable the SQL Server Audit and the Audit Specification.

d) We can then read the logged data using either event viewer (start/run/eventvwr) or using the Log File Viewer or a new function: fn_read_audit_file.

You can read more on SQL Server Audit at this technet link here.

One Response to “SQL Server Audit in SQL Server 2008”

  1. […] The answer to this question is Yes – a feature in SQL Server 2008 called “SQL Server Audit” allows us to do audits for the select statements as well but prior to SQL Server 2008, the only way to capture it was using a profiler trace.  Before we start digging into this capability, here is a previous blog post from us in which we had covered the auditing capabilities in Oracle – here and the ones in SQL Server – here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: