Developers and DBAs using Microsoft SQL Server always look for ways to log
events of interest to them as they occur on the server.
SQL Server '08 fulfills this need with the introduction of audits, where we
no longer have to write triggers and hooks to trap events of interest.
The Audit feature is however, available only in the Enterprise Edn.
Direct Hit! |
Applies To: Database developers and DBAs USP: Audit events of interest on a database server Primary Link: None Google Keyword: Audit Specification, Audit Log |
We take you through the steps to start auditing your server.
Creating an Audit object
First create an audit object. This essentially defines a destination to
which SQL Server will record information about interesting events that occur. An
Audit object can capture monitored events to either the file system or to the
event log, but for brevity, let's restrict this discussion to the file system.
Right click on the Audits folder of the instance level Security folder in SQL Server Management Studio (SSMS). Select 'New Audit'. |
Specify the name of the audit and file path. Select OK. When an audit object is first created, it is in a disabled state and will not audit any events until it is explicitly enabled. |
Right click on the audit object, select 'Enable Audit' and click on 'Close'. Once an audit object is created, you can define more audit specifications to monitor specific events. |
Auditing Server Events: Right click on 'Server Audit Specifications' and select 'New Server Audit Specification' (as shown above). |
Specify the name of the server audit specification & audit object to be used. Select events that you want to watch for and click on 'Ok'. Also enable Server Audit Specification by right-clicking. |
Auditing Database Events: Rright click on Database Audit Specifications in the Security folder of the database you are interested in, and select New Database Audit Specification. |
Now specify the name of the specification object, select the audit object and also select the events that interest you. |
Now you have to select the object and principal in each case. Select 'Ok'. Here also, you have to enable the audit specification. |
The Events: Now create a table and name the table as Employee. Enter a few records in the table. |
Viewing Audited Events:To view the audited events from the Management Studio, right click on the audit object and select 'View Audit Logs'. |
Amaresh Patnaik