sp_set_session_context (Transact-SQL)

Applies to: yesSQL Server 2016 (13.x) and later YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics

Sets a key-value pair in the session context.

Topic link icon Transact-SQL Syntax Conventions


sp_set_session_context [ @key= ] N'key', [ @value= ] 'value'  
    [ , [ @read_only = ] { 0 | 1 } ]  
[ ; ]  


[ @key= ] N'key'
The key being set, of type sysname. The maximum key size is 128 bytes.

[ @value= ] 'value'
The value for the specified key, of type sql_variant. Setting a value of NULL frees the memory. The maximum size is 8,000 bytes.

[ @read_only= ] { 0 | 1 }
A flag of type bit. If 1, then the value for the specified key cannot be changed again on this logical connection. If 0 (default), then the value can be changed.


Any user can set a session context for their session.


Like other stored procedures, only literals and variables (not expressions or function calls) can be passed as parameters.

The total size of the session context is limited to 1 MB. If you set a value that causes this limit to be exceeded, the statement fails. You can monitor overall memory usage in sys.dm_os_memory_objects (Transact-SQL).

You can monitor overall memory usage by querying sys.dm_os_memory_cache_counters (Transact-SQL) as follows: SELECT * FROM sys.dm_os_memory_cache_counters WHERE type = 'CACHESTORE_SESSION_CONTEXT';


The following example shows how to set and then return a sessions context key named language with a value of English.

EXEC sys.sp_set_session_context @key = N'language', @value = 'English';  

The following example demonstrates the use of the optional read-only flag.

EXEC sys.sp_set_session_context @key = N'user_id', @value = 4, @read_only = 1;  

See Also

Row-Level Security