1

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?

Thanks

Rob.

tr0users
  • 413
  • 3
  • 11
  • 16

1 Answers1

0

You should be able to use Change Data Capture to track the old and new values.

mrdenny
  • 27,174
  • 4
  • 41
  • 69