How should we capture the database user when using the entity framework on a middle tier?

Question

We are developing a service layer for a new system that will handle all interactions with the MSSQL (2005) database. We are a bit perplexed as to how to capture all of the 'who done it' information that is required by our users in some of our legacy audit tables. While we could pass in the users name that was modifying data and log the call, we have some legacy tables that we will be using which have triggers to capture the system_user on record inserts, updates, and deletes. We also enlist some row level security in some places that we would also like to leverage without changing the code if possible. I have read that some are using contextinfo to store the user, but that seems a little less than secure in this situation.

The option that I like best is using the execute as user on a per stored procedure call basis

execute sp_myproc @foo as user = 'username'

The problem that we are running into is that within the entity framework it does not appear to be possible to add the execute as commands to the stored procedure calls.

Thanks for any input.

Answer 1

"EXECUTE AS" does not support procedure calls (it would be in the actual proc definition). Only remote or dynamic SQL.

Options:

• Any middle tier has to pass the end user as a parameter. We do this for our web services and GUIs where there is no direct connection to the database

• Enable server delegation so each server can pass through the end user credentials. Basically "impersonation" in asp.net.

• Issue a separate command first

Answer 2

Have you tried SETUSER? http://msdn.microsoft.com/en-us/library/ms188315.aspx

Answer 3

Doug,

I assume that the mid tier on a different system, and that you need the user information on the database (i.e., not in the mid-tier). If this isn't correct, please let me know.

If you using Windows Authentication, you will need to set up delegation. This will allow you to impersonate the end user in the database. Are you using Windows Authentication?

Erick