Audit Trails and Logging

This week at SQLServerCentral there have been a couple of articles on Audit Trails and Logging (Part One and Part Two) that did a good job giving the basics of logging changes made to the data. In the discussion for part two someone asked about logging select statements and the returned results. While logging the selects is reasonable, logging the results of each query is, in my mind, a little out of whack. I understand why you may want to log the results as they may change over time. For example, Mary Smith married Joe Johnson and takes his last name, or hyphenates so she is now Mary Johnson or Mary Smith-Johnson. A query for LastName = 'Smith' the day before the change returns Mary, but the day after it does not. So in the event that data was compromised it will be more difficult to determine whose information was compromised if only the query is logged and not the data. There are several questions that come up now:

• How much space do you have for logging and is the expense associated with it justifiable?
• How far back do you keep the logged data?
  
• Do you log queries of the logged data?
• Who has access to the logged data?

These are questions that come up right off the top of my head and do not address the mechanics of the logging. There are no "Select" triggers in SQL Server so that eliminates triggers, which also eliminates being able to log ad-hoc queries using Access, SSMS, Query Analyzer, or any OLE DB or ODBC connection (Excel). One solution suggested in the discussion is using a server-side trace which works for logging the query and who ran it, but will not log the results. As I am a firm believer in not granting direct table access, I would start out by putting logging in the stored procedures used for data access. Of course, that can lead to some issues with developers who would prefer to use an ORM tool, like Linq To SQL or SubSonic, for the data access layer. Granted they are still able to use stored procedures through these tools, but one of the selling points is that you "never have to write another stored procedure". So now they not only have to data access code in the DAL, but also auditing code. If the audit needs to include the data, can I do a batch insert with the ORM tool using the dataset, list, collection I already have or do I have to iterate through the results and insert one at a time? Or do I have a separate call to the database that runs the select again, like:

Insert Into audit
Select
query,
user,
data columns....
From
table(s)
Where
criteria

This means now I am storing the user and query with each row returned and I am doubling the reads on my database.

I certainly don't have the solution to this problem. I know that for Inserts/Updates/Deletes, I like triggers because they put all the code in one place and will fire on all changes unless someone explicitly disables triggers before making a change and if this happens you have other problems.

I'd be interested in hearing what other people think.