I have a request to capture all SELECT statements issued against a table within a Database Snapshot. SQL Audit would make fullfiliing this request simple in any user-database, however it appears there is a limitation of SQL Audit when it comes to Database Snapshots, namely that SQL thorws an error if issuing CREATE DATABASE AUDIT SPECIFICATION against a Snapshot because it is read-only. Is this completely accurate or am I missing something about SQL Audit that may allow it?
As it stands, unless someone can point out how I can use SQL Audit with a Snapshot, I am considering implementing my own Extended Events session. Initially I thought I might inspect all sql statements that select from a specific table in the Snapshot by filtering on the text to capture only queries that reference the table in question. This response
from Jonathan Kehayias to a similar question talks about possibly using a technique where we would capture the "sqlserver.lock_acquired event with the sqlserver.sql_text and sqlserver.tsql_stack actions" which is very clever but probably will not work on a Database Snapshot because as I understand it no locks are taken in Snapshots because it is a read-only database. I'll be testing that theory out soon enough though.
I do not want to use Trace. Does anyone else have any ideas about how else might this be achieved using SQL Audit, Extended Events or another technique?
The platform is 2008 R2 Enterprise Edition.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato