Simple Example of Creating Database Audit Specification

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:[codesyntax lang=”tsql”]

USE master;

[/codesyntax]Or[codesyntax lang=”tsql”]USE master;GOGRANT CONTROL SERVER TO ;[/codesyntax]To configure database audit specification, user also must be granted the ALTER ANY DATABASE AUDIT, ALTER, or CONTROL permission on audited database:[codesyntax lang=”tsql”]USE ;GOGRANT ALTER ANY DATABASE AUDIT TO [/codesyntax]Or[codesyntax lang=”tsql”]USE ;GOGRANT CONTROL TO ;[/codesyntax]Or[codesyntax lang=”tsql”]USE ;GO GRANT ALTER TO ;[/codesyntax]To create a SQL Server Audit object, expand the Security folder in Object ExplorerRight-Click AuditSelect New AuditNew Server AuditIn 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[info] Note: to modify an audit, disable the audit first (right-click the audit and select Disable Audit), unless the changes will not be accepted. [/info]


2.      Create a Database Audit Specification

To create a database audit specification, expand intended database you want to audit in Object ExplorerExpand its Security folderRight-click Database Audit SpecificationClick New Database Audit SpecificationNew Database Audit SpecificationIn 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 WizardFor 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.


Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles


Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan