0

IN CDC, Is there any way by which, we can get information who(user) made changes in table. and by which system (computer name)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gaurav Srivastava
  • 11
  • 1

1 Answers1

0

No (see this post and this post).

You’d need a trigger, and then have to correlate changed rows between CDC and wherever your trigger logs actions. You could add a column to the table to make that easier, but then you’d only know who made the last change.

I think it would be a better approach to either add SQL Audit (though I don’t recall how good the functionality was 15 years ago, and whether what you need is supported by your edition) instead of a trigger, or force data changes through a stored procedure, where you can log as part of the action (eg using the OUTPUT clause).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thankyou Sir for response , I also do this ----- "You could add a column to the table to make that easier, but then you’d only know who made the last change." But when I delete record from table , I couldn't get actual user name who delete record. by this i only get last edit/insert user name..... – Gaurav Srivastava Jan 30 '22 at 14:28
  • Right, you need your delete trigger to log _somewhere else_ because if you’re relying on a column in the row and you delete the row… – Aaron Bertrand Jan 30 '22 at 15:05