SQL Server Database Audit feature in SQL SERVER 2014

Microsoft introduced audit feature in sql server 2008 which includes server and database level auditing. You can capture database objects, database level changes, lets say somebody deleted some records from your critical database, Bingo … means somebody is having DELETE permission on prod server apart from DBA, living in DANGER. As DBA we don’t allow such permission on production server.  Now you have to find which statement  is ran against your critical database. If you have enable Auditing on server, its gets logged  in Audit log file that gets created while configuring auditing on SQL Server. You can view or read this file and find the culprit.

We are going to look into database auditing feature in sql server 2014 environment.

sql server database audit specification

This is how its looks in SQL Server 2014 when you enable the database audit, We are working to see the simple example of SQL Server Database Audit feature which will capture the delete  statement against specific database.

SQL Server Database Audit feature with SQL Server 2014

Steps

  • Connect to SQL Server Instance using SSMS (SQL Server management Studio)
  • To enable Audits, Click on Security -> Right on Audits

Enabling Audit in sql server 2014

  • It will see create audit window, here give the Audit file and file path. You can set action in case Audit log failure andthe maximum size for audit file, here we are going with default setting. Click on OK

Create Audit using wizards

  • You will see audit file that will use later while configuring Database Audit.
  • Select your database, in my case it is named as Testdb.
  • Click on database -> security and Database Audit Specification

Creating new database Audit specification

  • Now select the Audit file that you created earlier steps while configuring Audits

Selecting Audit file to capture the details

  • In Actions, you need to select what type of audit do you need for your database in my case I am going with DELETE type.

Define audit action type and database object for Audit

  • Select object details, like table name and dbo schema and click OK

Selecting database table to audit

  • Now Right click on Database Audit specification that you see under your database security and select Enable Database Audit specification.

 

You are all done now. Lets try to delete some records from student_details table

delete statement

Run the below to verify whether  it came into the Audit file or not
SELECT event_time,action_id,statement,database_name,server_principal_name
FROM fn_get_audit_file(‘D:\DatabaseAudit\Audit-*.sqlaudit’,default, default)

Verify the Audit file using this query

BINGO …. Our job done, we can see the entry is logged into Audit log. As you can see Auditing is very easy to implement in SQL Server.

I hope, this article may help you to implement Auditing with SQL Server 2014 …. Internet is ocean of knowledge Keep on surfing 🙂

Leave a Reply

  

  

  


+ 3 = 8