I was doing a little testing of Row-Level Security (RLS) for the stairway, and one of the ways that some people implement RLS is with CONTEXT_INFO().

I haven’t every really used CONTEXT_INFO in production, though it’s been around for some time. This is a way of setting some session information, as this data is stored for the connection. However, since it can be reset by the connection, it’s value isn’t necessarily trustworthy from a system perspective.

If you don’t set this with SET CONTEXT_INFO, then a NULL is stored there for on-premises systems. This makes sense, there’s no initialization there.

In Azure SQL Database, however, you get a GUID that’s a unique value. That’s good to know, as if you’re checking if this is NULL, you might assume you have something stored there, and since you need to CAST this back to the original datatype, this might cause issues.

This could be a good way to store data for a single session, but beware. If the session drops and reconnects, you’ll lose your data.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.