Database Simplified Headline Animator

Database Simplified

Saturday, 8 October 2011

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

image

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

image

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 --->

image

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

image

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 :

image

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

3 comments:

  1. Hi friends,

    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. Thanks a lot......

    ReplyDelete

  2. An old, but very useful and informative article)
    Thanks to the author for such a good example.
    Richard Brown electronic data room

    ReplyDelete
  3. Having an audit looming over your head is enough to give even the most hardened individual the potential to break down in tears. independent auditing services uk

    ReplyDelete