Sql-server – Change Data Capture in a services environment

change-data-capturesql serversql-server-2012

Change Data Capture looks to be exactly what I need… Except.

All of my database actions come from users that system accounts. We have WCF service that is called by an application. The WCF Service does the authorization for the call, then the system account that the WCF Service runs as makes the needed call to the database.

This allows us to tightly control permissions on the SQL Server.

Because of this, Change Data Capture is only going to be recording that generic user's information.

But I need to know the actual user that initiated the WCF Call.

Is there a way to plug in an alternate user name for Change Data Capture? Or another way to get Column level logging of who made a change and when?

Best Answer

So, after a bit of thought, I realized that I can just treat my User Information as another piece of data.

So I can add to each table a "ChangedBy" and a "ChangedWhen" column. Whenever my service updates a record, it will update the ChangedBy and ChangedWhen. Then in the Change Data Capture history, the changes include needed data.

Related Solutions

Sql-server – Change data capture – how to know who made the change

I filed a bug about this, but it was closed as "by design."

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

Unfortunately you will have to use another technique (e.g. SQL Audit or a trigger) to get this information (and some custom code to try to correlate it to the CDC data as much as you can). I wrote about this deficiency also in my chapter "SQL Server Audit, Change Tracking, and Change Data Capture" in the book SQL Server MVP Deep Dives (volume 1).

I'm sorry that I don't have a better workaround for you but the built-in capabilities of CDC just aren't going to meet your requirements. :-(

Sql-server – Querying Change Data Capture data

You use built-in table-valued functions (TVFs) as described in Books Online : Using Change Data (MSDN).

First you need to find the LSNs you want to use to filter the ranges you want to query. These LSNs will represent points in the log which you can correlate to datetime values. You can do this using sys.fn_cdc_map_time_to_lsn. Let's say you want to report on all the change data captured today:

DECLARE 
  @s DATE = SYSDATETIME(),                  -- start today at midnight
  @e DATE = DATEADD(DAY, 1, SYSDATETIME()), -- end tomorrow at midnight
  @sl BINARY(10),                           -- start LSN 
  @el BINARY(10);                           -- end LSN

SELECT 
  @sl = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @s),
  @el = sys.fn_cdc_map_time_to_lsn('largest less than',              @e);

SELECT @sl, @el; -- start LSN and end LSN within today

Then you can use those LSN values in the cdc.fn_cdc_get_all_changes and cdc.fn_cdc_get_net_changes functions, depending on what you are after (let's say, for a table called dbo.floob):

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_floob(@sl, @el, 'all');

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_floob(@sl, @el, 'all');

Best Answer

Related Solutions

Sql-server – Change data capture – how to know who made the change

Sql-server – Querying Change Data Capture data

Related Question