Database Audit - Example with Select statement.

SQL SERVER audit is a server feature and its used to audit server actions.
Its available in SQL 2008 and later editions.
Its using SQL Server extended events to audit server activity.
Its providing much more granularity and we can use it to audit database related events also.


Let see


First we will enable server audit
1. SSMS -> Security -> Audit-> right click
Select new audit














2.Provide necessary details with Maximum Rollover files and Maximum file size details
Its required so that audit process doesn't fill disk space

3.Click OK
4.Right click and enable it

Now we will go to database on which we want to audit events
Here we will go to Adventureworks database

1.Security->Database Audit Specification->Righ click
select new database audit specification

2.set below option
  name:- select statement audit
  In Audit select Name :- Server audit
  In Audit Action Type:- Select
  OBJECT Class:- Database (we can select schema or object here)
  Object schema :- No need if DATABASE is selected
  Object Name :- Select AdventureWorks
  Principal Name:-DBO ( we can select other principal also as per requirement)

 Click ok

  Our database audit is ready
  Enable it

  Now we will run a query on Adventureworks database

  SELECT * FROM Sales.Store

  This statement should be capture in audit file
  But wait , it can be read in text or hex editor
  We have to use SQL  Server functions  to read the file

  File name will start with server as audit name is [server audit]
  and will have extension of sqlaudit.

  So fo reading file we will use wildcard to get filepath.
  Here its like this
  c:\Test\server*.sqlaudit

SELECT event_time,

       action_id,

       statement,

       database_name,

       server_principal_name

FROM   Fn_get_audit_file('c:\Test\server*.sqlaudit', DEFAULT, DEFAULT);

Here we can see that our select statement are logged in audit file with required information.

Its a good tool to audit various activities
I have given a sample here for you  to start with it.