Auditing In SQL Server

SQL Server 2008 Enterprise +

SQL Auditing: Auditing is most demanding activity as we need to audit many things time to time and on demand in any system. Starting with SQL Server 2008 Enterprise we have excellent audit feature that can be used to audit Server (SQL Instance)  ( Server Audit Specification) or Database Level Activity (Database Audit Specification). There are many action group on which auditing can be configured. We will demonstrate Creating Database audit (Instance Level) and Delete And Update DML(On Database Level).

Audit Can be Configured Using SSMS (SQL Server Management Studio) Or Using T-SQL Queries.

In Order To Create Server Audit Specification or Database Audit Specification We Need To Create SQL Server Audit Objects.

The SQL Server Audit object captures SQL instance or database-level actions and groups of actions. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance.

Create A Server Audit

Select Server Instance –> Security –> Audit—>Right Click and Select New Audit

When you define an audit, you specify the  audit destination. Audit destination can be (File, Security Log, Application Log) , For This Demonstration I am using File.  You can specify Audit Name, Audit Destination, Audit File Path, Max Size of Audit File. So Once you click Ok Your Server Audit Created But by default it is disabled so you need to enabled it by right clicking on audit created and choose Enable Audit.

Now your server audit is ready you need to configure Server Audit Specification to Capture Create or Drop Databases.

Select SQL Instance –> Security –> Server Audit Specification –> Right Click and Choose New Server Audit Specification

You Will Get The Following Dialogue Box as Above, You Can Select The Server Audit and In Audit Action Type You Can Select Multiple Audit Action I have selected Database_Change_Group Since we need to Audit Create And Drop Database in SQL Server Instance.

 

Now Click Ok. and Same As Server Audit These Server Audit Specifications Are Disabled By Default So You Need to Enable Them.

---

Now Set Database Audit Specification For Insert, Delete And Update. When you want to define Database Audit Specification then you need to set it for individual database.

To Check Database Audit Specification Create A Database With Following Script

Create Database ON
Go

Use ONP
Go 
Create Table OrderMaster
(
Id Int Identity(1,1),
OrderNo Varchar(20),
CompanyName Varchar(50),
OrderDate DateTime
) 
Go

Select SQL Instance —> Databases –-> Select Database on which Audit Has To Be configured --->

For DML Audit I have Selected DELETE, UPDATE, INSERT Audit Action on Database Audit Specification. Under Object Class I have Selected Database Which means DML on any table in the database. You can select a Particular object or Particular Schema For Audit. Next is Object Name Which is related to Object Class Since Class is Database so Object name will be a database Name. So If any DML takes place in any table under ONP Database those action will be audited. The Last One Is Principal using principal you can set the audit for a particular user or a particular role. I have given public as every user is tagger to this role. so in this case action from every user will be audited.

 

Now Click Ok and And Enable the Database Audit Specification same as we did for Server Audit and Server Audit Specification.

Now open A Query Window And Run Following Commands

--To Generate Event for Database Chagen Group

Use Master
Create Database MyTest
Go 
Drop Database MyTest
Go

--For DML Operation On ONP Database

Use ONP
Go
Insert Into OrderMaster Values
('EO340','ASOS.Com',GETDATE()),('EO341','Next.Inc',GETDATE())
Update OrderMaster Set CompanyName='ASPL' Where Id=1
Delete From OrderMaster Where Id=1
Go

Now You Check You Audit 

SQL Instance –> Security –> Audit –>Right Click On Audit  (“My_Local_Instance_Audit” we created it for demonstration) –> and Choose View Audit Logs.

You will Get Following Screen

You can see that every audit operation has been logged aand show command executed, user name and date and etc. You Also View Audit Log With System Function

Select * from Sys.fn_get_audit_file('FleName',default,default)

How to Know File Name ? You can get the file name by checking the path given in Server Audit the extension of Audit File Is  .sqlaudit

Select event_time,action_id,server_principal_name,statement,* 
from Sys.fn_get_audit_file(
'D:\DBBack\Audit_My_Local_Instance_F7C6701F-42C0-4096-8E7B-676785E65645_0_129625425056510000.SQLAUDIT',
default,default)

If You Have Multiple Audit Files then You Can Use Following

Select event_time,action_id,server_principal_name,statement,*
from Sys.fn_get_audit_file('D:\DBBack\*.sqlaudit',default,default)

Output :

So auditing now is very easy and flexible. There is long list of Actions which can be audited.