I'm designing an application that will run on SQL Server 2008 R2. There's a requirement to audit any updates to a customer table. The new Audit service that was release as part of SQL Server 2008 seemed to be a great fit. Unfortunately, although I have been able to setup a Database Audit Specification so that it logs details of any update - it does not log parameters to the update. For example, I ran the following query:
update Sales.Customer set CustomerType = 's' where CustomerID = 1
The log recorded only the following:
Statement UPDATE [Sales].[Customer] set [CustomerType] = @1 WHERE [CustomerID]=@2
I would have thought that the ability to log what had changed should be possible through the audit service? Without this capability, the benefit of the Audit service seems limited. Am I missing something?