Database Audit Speicification:Track Insert, Select and Delete in a database.

Problem: How to track all DDL/DML statements issued in the user created database only by users in a role.

Solution: CREATE DATABASE AUDIT SPECIFICATION

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. The SQL Server Audit object collects a single instance of server- or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. The Database-Level Audit Specification object belongs to an audit. You can create one database audit specification per SQL Server database per audit. 

Syntax:

CREATE DATABASE AUDIT SPECIFICATION audit_specification_name 

{ 

    FOR SERVER AUDIT audit_name  

        [ { ADD ( { <audit_action_specification> | audit_action_group_name } )  

      } [, ...n] ] 

    [ WITH ( STATE = { ON | OFF } ) ] 

} 

[ ; ] 

<audit_action_specification>::= 

{ 

      action [ ,...n ]ON [ class :: ] securable BY principal [ ,...n ] 

} 

Steps to follow for creating a database Audit:

      1.     Create a Server Audit and map the output file it to a folder where you can access.
2.     Create a Database Audit and map to the server audit created in step1
3.     Enable Server Audit
4.     Enable Database Audit
5.     Test the database audit.

Example: In this example, I would like to show we can track all the deletes on a database by creating a database audit specification.

USE [master]

GO

-- Creating Server audit and mapping to local folder

CREATE SERVER AUDIT [AudittoTempFolder]

TO FILE

(              FILEPATH = N'C:\Temp\' )

--Enable the server audit

ALTER SERVER AUDIT [AudittoTempFolder] WITH (STATE = ON)

GO

OUTPUT: 

-- Create Database Audit With Server Audit to Match the previous one created.

USE [Test]

GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseDeleteAuditSpecification]

FOR SERVER AUDIT [AudittoTempFolder]

ADD (DELETE,INSERT,SELECT ON DATABASE::[Test] BY [dbo])

WITH (STATE = ON) --Enable the database Audit

GO

OUTPUT:

After creating a database audit specification there should be an empty audit file created in the local folder where you have mapped the server audit.