2

I'm looking for an auditing solution that does exactly what Change Data Capture (CDC) does, except I need it to also track the application user that made the change. I'm currently using SQL Server 2012 Enterprise and may be upgrading to 2014 later this year.

We already have an auditing solution in place that leverages Delete, Insert, and Update triggers, but some new requirements might force us to update every audit trigger and corresponding audit table. Given various problems we've run in to with that solution over the years, this seems like as good a time as any to reevaluate and potentially replace the solution.

To give you an idea of what I'm currently working with (and may be able to leverage), we use a stored procedure (ConnectionInitialize) to store a user id with a SPID in a table (ApplicationUser) and then we delete the row using another stored procedure (ConnectionReset) once we're done making our deletes, inserts, and updates.

Were we to use CDC, I looked into adding a trigger to something like the cdc.lsn_time_mapping table, but I couldn't find a way to map the LSN back to the SPID (and therefore the user id) that was being used. This also presented some other issues in that CDC is always a little bit behind.

I looked into SQL Server Audit a little bit, but that presented some challenges of its own. We're using Transparent Data Encryption (TDE) to appease some of our security requirements, but SQL Server Audit looks like it'd need a separate encryption strategy; that and I'm more interested in the columns than in the actual SQL statements. Even so, these aren't deal-breakers for me, so I'm still looking into it.

Given what I'm trying to accomplish, does anyone have any feedback or recommendations?

Wade
  • 512
  • 3
  • 10
  • 2
    Have you tried using triggers and the CDC both together. Put a column on the table and store the SPID whenever there is a change by using triggers, then use the CDC to retrieve exactly who done what. – Luiz Felipe Apr 23 '14 at 17:52
  • This is somewhat similar to the current auditing we have in place, we have some 70+ triggers across all our tables to do this currently. We would need to alter all of our tables plus all those triggers to get this particular solution to work. Although viable, not likely. I was hoping for a one stop shop type of solution but it looks like it just may not be possible! We had thought possibly of using a mapping table to the CDC's transaction ID and the application IDs, anyone tried this? – Wade Apr 23 '14 at 19:56

2 Answers2

2

By itself, CDC doesn't meet the requirements. The reason being is that CDC only grabs changes to your data, not any underlying context under which those changes were made. You can, however, get what you're looking for if you're willing to tag your data with some audit columns. The basic idea is that you append a column to your table (or to a different table if you aren't able to modify the actual table for whatever reason) and populate it with the user who last modified the record (pretty simple to do via an insert/update trigger). Once that is actual data, you can consume it however you need to (CDC being one possible mechanism).

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Does this solution handle auditing deletes as well? – Wade Apr 23 '14 at 18:51
  • 1
    It will only handle deletes if you choose the "side table" solution since you can't tag the data with who deleted it since it will be gone. :) – Ben Thul Apr 24 '14 at 02:50
  • You can have IsActive column to have soft delete. when there is an attempt to actually delete record, you can simply update the flag IsActive = false, instead of deleting the record – Venkataraman R Jun 04 '19 at 12:02
2

Late answer but hopefully useful.

There is a third party tool, ApexSQL Audit, capable of meeting your requirements. My previous company is using it for years and they have been satisfied with it.

There is a helpful comparison article you can read to find more details about audited data, auditing mechanisms, integrity protection etc, for both CDC & Audit tool at one place.

Dorian Popescu
  • 121
  • 3