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.