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
Create Table OrderMaster
Id Int Identity(1,1),
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
Create Database MyTest
Drop Database MyTest
--For DML Operation On ONP Database
Insert Into OrderMaster Values
Update OrderMaster Set CompanyName='ASPL' Where Id=1
Delete From OrderMaster Where Id=1
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
If You Have Multiple Audit Files then You Can Use Following
So auditing now is very easy and flexible. There is long list of Actions which can be audited.