How to pass out-of-band (current User Id) data to SQL Server 2008.

Question

We have a web application that uses forms authentication to authenticate a user based on a user table in the database. (I.e. no active directory or SQL server user accounts are involved here). The web application accesses the SQL server using a service account. However, for auditing, authorization and other purposes, our stored procedures need to know for which user any given operation is being executed.

In a previous life, I worked with a similar situation using an Oracle database. In this scenario, every time we opened a connection, we first called an Oracle build in procedure to set a connection scoped context variable. This variable contained the user id for the user that would be using the connection. Then all stored procedures that needed to know the current user would check the context variable.

Conceptually this worked a lot like pushing user information onto the CallContext before making a remote call.

My question is, is there any similar mechanism in Microsoft SQL server?

Obvioulsy, if I must, I can pass the UserId as an argument to every single stored procedure, but that is exactly what I am trying to avoid.

Answer 1

What you can do is create users within the database (without Server logins) and give them appropriate permissions. After that, what you do is an "execute as" statement and then the user context for your database calls will be as if the user called it. Example:

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

Downside: you have to set up SQL security and manage users Upside: Users cannot connect directly to SQL Server and you get auditing.

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

See examples towards the bottom of the page.

Answer 2

Use SET CONTEXT_INFO and CONTEXT_INFO(). See Using Session Context Information.