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.

http://sqlserveruniverse.com/utility/images/article_images/ADMN01003_05.png

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.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |