sql-server - 如何将带外(当前用户 ID)数据传递给 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

使用SET CONTEXT_INFO和 CONTEXT_INFO() .参见 Using Session Context Information .