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:
GRANT ALTER ANY SERVER AUDIT TO ;
USE master;GOGRANT CONTROL SERVER TO ;
USE ;GOGRANT ALTER ANY DATABASE AUDIT TO
USE ;GOGRANT CONTROL TO ;
USE ;GO GRANT ALTER TO ;
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 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
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.