Auditing in SQL Server 2008

Dropping/Altering Server Audits

Audits can be modified or dropped by using the below code:

ALTER SERVER AUDIT [Audit-20080831-140855]



DROP SERVER AUDIT [Audit-20080831-140855]


You can alter the server audits after creating them. However, before altering them you should disable them. Otherwise, an error will be returned. Same can be said with dropping.

Auditing Specifications

Now we have created the audit location, we need to create the auditing specifications. There are two types of auditing specifications.

1. Server Audit Specification

2. Database Auditing Specification

Server Audit Specification

You can create only one Server Audit Specification for one audit.

Server audit will focus on the server level events such as logon/logoff operations, database backup/restore, database maintenance operations, server objects changes, permission changes etc.

There are several frequently used Server Audit Specifications groups which are listed in following table.

Action group name



Indicates that a principal has successfully logged in to SQL Server.


Indicates that a principal has logged out of SQL Server.


Indicates that a principal tried to log on to SQL Server and failed.


This event is raised whenever a login’s password is changed by way of ALTER LOGIN statement or sp_password stored procedure


This event is raised whenever a password is changed for an application role.


This event is raised whenever a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures.


This event is raised whenever a login is added to or removed from a database role.


This event is raised whenever a backup or restore command is issued.


This event is raised whenever a principal issues any DBCC command.


This event is raised when Security Audit operations such as altering settings, resources, external access, or authorization are used.


This event is raised when operations in a database, such as checkpoint or subscribe query notification, occur. This event is raised on any database operation on any database.


This event is raised when a database is created, altered, or dropped.


This event is raised when principals, such as users, are created, altered, or dropped from a database.


This event is raised when a GRANT, REVOKE, or DENY is issued for permissions in the server scope, such as creating a login.


This event is raised whenever a GRANT, REVOKE, or DENY is issued for a server object permission by any principal in SQL Server.


This event is raised whenever a GRANT, REVOKE, or DENY is issued for a statement permission by any principal in SQL Server

As with creating Audits,  we can create Server Audit Specifications from either SSMS or T-SQL.

In  SSMS, there is new node called Sever Audit Specifications, where you have to right click and create the new Server Audit Specifications.

You will get the following screen after selecting new Server Audit Specifications.

In this, select audit specification name and the audit name.

After that you have to define Audit groups. In the above example I have added five groups which are frequently monitored in my working environment.

The Server Audit Specification must then be Enabled. Altering and dropping audit specifications is done in a similar manner to Audits.

Audit specifications can also be created from T-SQL , as in the below script  sample:

USE [master]


IF  EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N’ServerAuditSpecification-20091126-130056′)

DROP SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20091126-130056]


USE [master]


CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20091126-130056]

FOR SERVER AUDIT [Audit-20091125-113200]




Viewing Audit Results

Right click the relevant Server Audit and select View Audit Logs , the below screen should be visible.

As this is the normal SQL Server editor, you can use the exporting, searching and filtering functions. If your audit file is using a file instead of Security log or application log, the data will be much clearer.


Pages: 1 2 3


No comments yet... Be the first to leave a reply!