Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer
    
If it were'nt closed, I'd have voted for it. Back to those $%#@## triggers I guess. –  RThomas Feb 4 '13 at 22:37
    
@RThomas you can still vote for items even if they're closed - they do get revisited occasionally. Even more useful than voting is to add a comment stating the details of the business need that would make this information useful, including maybe the cost of refactoring things to work around the missing functionality. I wrote up a bit about how the qualitative data can outweigh the quantitative data... –  Aaron Bertrand Feb 4 '13 at 22:40
    
Good to know, I thought closed meant closed to voting as well. I pointed out in my comment that Oracle offers this in their CDC implementation. It sorta seems logical. docs.oracle.com/cd/A91202_01/901_doc/server.901/a90237/… –  RThomas Feb 4 '13 at 23:42

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

SET @CapturedBy = CAST(REPLACE(CAST(CONTEXT_INFO() AS VARCHAR(10)),CHAR(0),'''''''') AS INT)

IF(@CapturedBy) IS NULL
    BEGIN
       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
    END
share|improve this answer

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.

share|improve this answer
    
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 '13 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 '13 at 15:06
    
Ahhhh, didn't catch that. I get what your saying now. –  RThomas Nov 3 '13 at 23:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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