Is it possible to audit query results in SQL Server?

Question

I want to find out if it is possible to audit the results of any query that gets executed on an instance of SQL Server. We currently have C2 auditing enabled, but this only showed what query was executed, not what was returned.

Since this project is health-information related, we want to ensure that we are auditing to the most granularity we can obtain to meet HIPPA requirements. Previously this was not required, but the nature of our project is changing.

The goal is: if a user logs into our system and executes a query, we want to know every patient that was returned from that query.

Is there either a built-in feature (triggers?) or a third-party application that we can use to achieve this? We looked into ApexSQL, but their customer service relayed that their software does not audit at that granularity.

Could this audit be restricted to the Patients table only?

Unfortunately no, due to the fact that if you were to query a different table, there is patient-related information; so if you happen to know that patient's foreign key, you could "get around" the audit by looking up individual clinical records.

We're not concerned about space or speed at this point in time.

• Zikato: Can you provide more information? How does the user query the patient? Can the user query multiple patients at once? How is the auditing information going to be used?

Answer 1

You could (a) force data access through a stored procedure, then (b) that stored procedure would dump the results to a #temp table before returning them to the user, then (c) the procedure would log the results to some background auditing table.

You'd want to rotate that though because it's going to get very big very fast, not to mention this will not exactly make queries faster. - aaron-bertrand