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.
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
- 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
- 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
- Now select the Audit file that you created earlier steps while configuring Audits
- 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.
- Select object details, like table name and dbo schema and click OK
- 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
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)
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 🙂