I am working with bringing data in from a source system and storing the data in the data warehouse.
I am using change tracking to run incremental daily loads and only bring over changes.
The change table returns the primary key for the row that was changed and the change columns SYS_CHANGE_VERSION, SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT.
I would like to bring in additional information for tracking purposes, I am trying to bring in the time that the change occurred and the user that made the change.
I think that I found the time part by connecting to the sys.dm_tran_commit_table view, but I cannot find a way to get the user that made the change.
Is there a way to connect the user that made the change to the change logged in the change table?