SQL Server 2008 - Audit SELECT performed on particular table

Trigger will fire for INSERT/UPDATE/DELETE  statements,not for SELECT statement

So SQL Server Profiler was used as the only way to audit SELECT statements,prior to SQL Server 2008.

SQL Server 2008 Enterprise edition helps us to audit SELECT statement performed on a particular table as shown in below example:

Step 1:

           Under Object Explorer,create a new Audit object as shown in below image:

Step 2:

               Mention Audit name,configure Audit settings as needed and Audit log destination type and destination path,if file is selected as Audit destination.

Step 3:

Create new database audit specification as shown below

 Step 4: 

To audit SELECT performed on particular table,try as shown below

      

Step 5:

Enable Audit objects as shown in below image:

    

Step 6  :

Now,try SELECT statement on the table on which audit is created.



USE AdventureWorks2012
GO

SELECT * FROM [Sales].[CreditCard]

Step 7 :

To view the audit log,try as shown in below image:

Step 8 :

 To read .sqlaudit file,try the below query 

SELECT * FROM fn_get_audit_file('C:\Users\Sathya\Desktop\Audit-SELECT-[Sales]%8[CreditCard]_F27B509C-3EE7-455E-85B8-58A5DEC23425_0_129970432923950000.sqlaudit', default, default)