title: SQL Server Auditing Alternatives with Application User Tracking
body: <p>I'm looking for an auditing solution that does exactly what Change Data Capture (CDC) does, except I need it to also track the application user that made the change.  I'm currently using SQL Server 2012 Enterprise and may be upgrading to 2014 later this year.</p>
  
  <p>We already have an auditing solution in place that leverages Delete, Insert, and Update triggers, but some new requirements might force us to update every audit trigger and corresponding audit table.  Given various problems we've run in to with that solution over the years, this seems like as good a time as any to reevaluate and potentially replace the solution.</p>
  
  <p>To give you an idea of what I'm currently working with (and may be able to leverage), we use a stored procedure (ConnectionInitialize) to store a user id with a SPID in a table (ApplicationUser) and then we delete the row using another stored procedure (ConnectionReset) once we're done making our deletes, inserts, and updates.</p>
  
  <p>Were we to use CDC, I looked into adding a trigger to something like the cdc.lsn_time_mapping table, but I couldn't find a way to map the LSN back to the SPID (and therefore the user id) that was being used.  This also presented some other issues in that CDC is always a little bit behind.</p>
  
  <p>I looked into SQL Server Audit a little bit, but that presented some challenges of its own.  We're using Transparent Data Encryption (TDE) to appease some of our security requirements, but SQL Server Audit looks like it'd need a separate encryption strategy; that and I'm more interested in the columns than in the actual SQL statements.  Even so, these aren't deal-breakers for me, so I'm still looking into it.</p>
  
  <p>Given what I'm trying to accomplish, does anyone have any feedback or recommendations?  </p>