Lee, I presume you're working with a healtcare system or somewhere outside the US.

There may be some healthcare specific products out there.

Logically, capturing the "pipe" to (and from) the database on the server (similar to an ODBC trace) would probably be the "safest". However, the ODBC trace itself is client-side (and is configured on the client), so it is not "safe" for audit purposes. Several years ago, I did hear of applications that captured the IP traffic for this purpose. It would probably have to be captured on the SQL Server.

If the requirement is application specific and the application is to be developed, the easiest way would probably be to architect your application with a layer that allows you to perform additional actions like capturing the results. However, the overriding issue may be how to ensure that all paths to the data is covered (other apps, ad hoc queries).

Consider using security settings limited to a service account known only to your company's security administration group, and require all access to the database to go through a separate service running under this account. That service could then perform any logging required. You would have to notify the service who the actual user is, so that this can be logged.

If this is not an option, it *may* be possible to implement a proxy database ("log and forward") or alternatively, maybe a SQL trace file approach could be combined with a trace scanner that would repeat the SELECT queries to capture the results (against a replicated DB?). This would have some challenges of its own, I suspect.