Greg Grater (8/29/2011)

---

Another great use for CONTEXT_INFO is auditing actions. Most web/windows applications create a helper class that manages opening a connection to the database. You can augment this helper class to store all kinds of session information by simply setting that information immediately after opening the connection.

For example. typically, the database connection is created through integrated security based on the app's security context which does *not* include the actual user's login. The user log's in through a login badge (forms based security) to the web app and the web app connects to the database through a common connection string.

I've used the common connection helper in the web app to inject a simple set of the CONTEXT_INFO session variable to store their Forms Based Security login ID. Then, as each stored procedure is called *with* that connection, I can pull the application login from CONTEXT_INFO and store it with the record that was updated or inserted (or even logically deleted). ...Instant Auditing of which user changed the data without the overhead of passing the information with every call.

Essentially, if you create a common connection helper class in your application, you can capture all kinds of session information in Context_INFO and use that information on each subsequent call to the database through that connection. (Think IP Address, Browser info, etc.)

Great post about a little known, but very powerful SQL Server session variable.