by September 3, 2009 0 comments

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
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
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

No Comments so far

Jump into a conversation

No Comments Yet!

You can be the one to start a conversation.