How should we capture a database user when using an entity structure at the middle level?

Question

We are developing a service level for a new system that will handle all interactions with the MSSQL (2005) database. We are a little puzzled by how to capture all the information about who did this, which is required by our users in some of our previous audit tables. Although we could pass on a username that modified the data and recorded the call, we have some outdated tables that we will use that have triggers to capture system_user when inserting, updating, or deleting records. We also involve some security levels at some levels, which we would also like to use without changing the code, if possible. I read that some of them use contextinfo to store the user, but in this situation this seems a little less secure.

The option that I like most is to use execution as a user in the database for each call procedure

execute sp_myproc @foo as user = 'username'

The problem we are facing is that within the entity framework it is not possible to add execute commands as commands to stored procedure calls.

Thanks for any input.

Answer 1

EXECUTE AS does not support procedure calls (this would be in the actual proc definition). Remote or dynamic SQL only.

Parameters:

• . - ,

• , . "" asp.net.

Answer 2

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

Answer 3

,

, (.. ). , .

If you use Windows authentication, you need to configure delegation. This will allow you to personalize the end user in the database. Do you use Windows Authentication?

Eric