Developer: Tips to audit SQL server databases

Tips to audit SQL server databases

With the new 'Audit' feature in SQL Server '08, Database Admin can implement strategy to meet the specific risks of their SQL Server '08 databases

Saturday, September 26, 2009

BANGALORE, INDIA: 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.

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