Tracking who made the change identified by CDC.

Along the lines of my datetime hack I tried the same approach by adding suser_sname as a new field with default value on the cdc change track table. But, that seems to return the owner of the cdc process and not the user who initiated the change on the base table. I also tried original_login but that returns the sql service account login. Again, likely associated with the cdc process and not the user who initiated the change.

I found a similar question on stack-overflow but with no answer other than tracking changes from the front end or via a trigger, which seems to defeat the purpose of using cdc. I wouldn't repost but since the original was on stackoverflow I thought I'd give it a try here especially if R2 or 2012 has introduced a better way.

So, in short: How do I know who made the change in change data capture?

3 Answers 3


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


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. :-(


You could add the column and have a trigger on the table to populate the user on insert/update/delete and cdc would then store this. You can grab the username either from the UI by passing the username using context info or from the actual session


IF(@CapturedBy) IS NULL
       SELECT @CapturedBy = P.[Code]
       FROM sys.dm_exec_sessions AS S
         INNER JOIN [dbo].[Person] AS P
          ON S.[login_name] = P.[User_Name]
       WHERE S.[session_id] = @@spid

Could you add a UpdatedBy field on the main table, set it to default to SUSER_NAME() or ORIGINAL_LOGIN() and have that data populated by the CDC? I believe that will give you the same information you're looking for.

  • That only gets you the svc account/owner of cdc, you might note that I tried those things and documented the results in my original question way back in Feb.
    – RThomas
    Nov 1, 2013 at 13:45
  • Your comment said you added it to the ChangeData table. I'm talking about the original table the ChangeData table is based on. However I realized after I posted this would only work for inserts
    – Chef
    Nov 1, 2013 at 15:06
  • Ahhhh, didn't catch that. I get what your saying now.
    – RThomas
    Nov 3, 2013 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.