How to transfer data out of range (current user ID) in SQL Server 2008

Question

We have a web application that uses forms authentication to authenticate a user based on a user table in a database. (For example, there are no active directory accounts or SQL servers). The web application accesses the SQL server using the service account. However, for audit, authorization, and other purposes, our stored procedures must know for which user any given operation is performed.

In a previous life, I worked with a similar situation using an Oracle database. In this case, every time we opened the connection, we first called the Oracle build procedure to set the connection context context variable. This variable contains the user ID for the user who will use the connection. Then, all stored procedures that the current user should know will check the context variable.

Conceptually, this worked the same way as dragging user information into a CallContext before a remote call.

My question is, is there a similar mechanism on a Microsoft SQL server?

Obvioulsy, if I should, I can pass UserId as an argument for each stored procedure, but this is exactly what I'm trying to avoid.

Answer 1

Use SET CONTEXT_INFOand CONTEXT_INFO(). See Using session context information .

Answer 2

What you can do is create users in the database (without logging into the server) and grant them the appropriate permissions. After that, you execute the execute as statement, and then the user context for calls in the database will be as if the user had called it. Example:

EXECUTE AS USER = 'user2';
EXECUTE usp_insert_stuff @params;
REVERT;

Downside: you need to configure SQL security and manage users Potential: users cannot directly connect to SQL Server, and you get an audit.

: http://msdn.microsoft.com/en-us/library/ms188354.aspx

. .