Change Data Capture looks to be exactly what I need... Except.
All of my database actions come from users that system accounts. We have WCF service that is called by an application. The WCF Service does the authorization for the call, then the system account that the WCF Service runs as makes the needed call to the database.
This allows us to tightly control permissions on the SQL Server.
Because of this, Change Data Capture is only going to be recording that generic user's information.
But I need to know the actual user that initiated the WCF Call.
Is there a way to plug in an alternate user name for Change Data Capture? Or another way to get Column level logging of who made a change and when?