Simple Example of Creating Database Audit Specification

The estimated reading time for this post is 2 minutes

Sometimes we are required to enable auditing because of many reasons such as monitoring the data modification, tracking the user activities through the system and even troubleshooting.

In SQL Server 2008 auditing has been introduced and throughout the years auditing feature is improved in later versions. auditing can be implemented in two levels. server audit specification and database audit specification. This article is to show the simple example of database audit specification.

You can create one database audit specification per SQL Server database per audit.

To create the Database Audit Specification, follow these steps:

1.      Create an Audit Object

To be able to enable, create, delete and modify server audit objects, a user must be granted the ALTER SERVER AUDIT or CONTROL SERVER permission:

USE master;
GO
GRANT ALTER ANY SERVER AUDIT TO <AuditConfigurationLogin>;

Or

USE master;
GO
GRANT CONTROL SERVER TO <AuditConfigurationLogin>;

To configure database audit specification, user also must be granted the ALTER ANY DATABASE AUDIT, ALTER, or CONTROL permission on audited database:

USE <DatabaseName>;
GO
GRANT ALTER ANY DATABASE AUDIT TO <AuditConfigurationUser>

Or

USE <DatabaseName>;
GO
GRANT CONTROL TO <AuditConfigurationUser>;

Or

USE <DatabaseName>;
GO GRANT ALTER TO <AuditConfigurationUser>;

To create a SQL Server Audit object, expand the Security folder in Object Explorer

Right-Click Audit

Select New Audit

New Server Audit
image-3862

In Create Audit dialog box, specify the audit name, audit destination, and path. Other options are:

Queue Delay – sets the number of milliseconds before the audit information is processed into a target file. When set to 0, the process is sync.

On Audit Log Failure

                continue: SQL Server operations continue. audit records aren’t retained.

                Shut down Server: Forces a server shot down when audit can’t right to the target.

                Fail Operation: No audited events occur. Actions which don’t cause audited events can continue.

Maximum Rollover Files – the number of files kept in the system. When the maximum number is reached, the new files overwrite the oldest ones. The default is unlimited.

Maximum Files – the number of files kept in the system. When the maximum number is reached, storing new audit information will fail.

Maximum File Size (MB) – the size of the target file. When the specified size is reached, a new file is created. The default is unlimited.

Right-click the created audit and select Enable Audit

Note: to modify an audit, disable the audit first (right-click the audit and select Disable Audit), unless the changes will not be accepted.

 

2.      Create a Database Audit Specification

To create a database audit specification, expand intended database you want to audit in Object Explorer

Expand its Security folder

Right-click Database Audit Specification

Click New Database Audit Specification

New Database Audit Specification
image-3863

In the Create Database Audit dialog box, specify the audit specification name, select the audit object which you create before, and select the events you want to audit in Actions box. In this example, we will audit:

  • Whenever a backup or restore command is issued
  • Whenever an INSERT on HumanResources schema is issued
  • Whenever an UPDATE on SalesOrderHeader is issued
  • Whenever a DELETE on AdventureWorks is issued

Database Audit Wizard
image-3864

For more information about database audit action types, see Database-Level Audit Action Groups and Database-Level Audit Actions on MSDN.

Right-click the created audit specification and click Enable Database Audit Specification.

 

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2015. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website


Leave a Comment

avatar
1000
  Subscribe  
Notify of