0

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.

bumped to the homepage by Community 16 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.