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]

WITH
(STATE
= OFF);

GO

DROP
SERVER AUDIT [Audit-20080831-140855]

GO

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	Description
SUCCESSFUL_LOGIN_GROUP	Indicates that a principal has successfully logged in to SQL Server.
LOGOUT_GROUP	Indicates that a principal has logged out of SQL Server.
FAILED_LOGIN_GROUP	Indicates that a principal tried to log on to SQL Server and failed.
LOGIN_CHANGE_PASSWORD_GROUP	This event is raised whenever a login’s password is changed by way of ALTER LOGIN statement or sp_password stored procedure
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP	This event is raised whenever a password is changed for an application role.
SERVER_ROLE_MEMBER_CHANGE_GROUP	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.
DATABASE_ROLE_MEMBER_CHANGE_GROUP	This event is raised whenever a login is added to or removed from a database role.
BACKUP_RESTORE_GROUP	This event is raised whenever a backup or restore command is issued.
DBCC_GROUP	This event is raised whenever a principal issues any DBCC command.
SERVER_OPERATION_GROUP	This event is raised when Security Audit operations such as altering settings, resources, external access, or authorization are used.
DATABASE_OPERATION_GROUP	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.
DATABASE_CHANGE_GROUP	This event is raised when a database is created, altered, or dropped.
DATABASE_PRINCIPAL_CHANGE_GROUP	This event is raised when principals, such as users, are created, altered, or dropped from a database.
SERVER_PERMISSION_CHANGE_GROUP	This event is raised when a GRANT, REVOKE, or DENY is issued for permissions in the server scope, such as creating a login.
SERVER_OBJECT_PERMISSION_CHANGE_GROUP	This event is raised whenever a GRANT, REVOKE, or DENY is issued for a server object permission by any principal in SQL Server.
DATABASE_PERMISSION_CHANGE_GROUP	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]

GO

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

DROP
SERVER AUDIT SPECIFICATION
[ServerAuditSpecification-20091126-130056]

GO

USE
[master]

GO

CREATE
SERVER AUDIT SPECIFICATION
[ServerAuditSpecification-20091126-130056]

FOR
SERVER AUDIT [Audit-20091125-113200]

ADD
(BACKUP_RESTORE_GROUP),

ADD
(FAILED_LOGIN_GROUP)

WITH
(STATE
= OFF)

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.