SQL 2008 Change Data Capture (CDC) Questions

[ousoonerjoe]

We recently upgraded from SQL Server 2005 to 2008.

There's still some things that are a little fuzzy. For instance, I found it is possible to join against the cdc.lsn_time_mapping table to find the time the action took place.

Is there a place where User and/or Host Name are captured?

How bad, if any, are the impacts of adding a column with default values to the dbo_TableName_CT table to capture the UserName/HostName data?

Is there a place that captures what caused the action (ie: Stored Procedure, Trigger, direct call, etc.)?

Is there a specific call I can make to get this information and insert it into the table like above with the UserName?

Google searches have not exactly been forth coming with these answers. CDC so far is great, but needs just a little fine-tuning to make it perfect for our needs.

Any and all suggestions and advice is highly welcomed.

Thank you.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[ousoonerjoe]

UPDATE:

After some testing, Adding columns with a default value do not work. They simply return the server name and internal user name. Which implies it is fired by the system and not as a trigger on the table (for instance).

It did not complain about adding the columns and triggers to a CDC table.

If anyone knows of a way to find the User, HostName, and procedure/CmdText that caused the action, I would be HIGHLY appreciative.

thank you.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------