8

I found that the following query shows how many session context variables was set:

SELECT cache_address
      ,name
      ,pages_kb
      ,pages_in_use_kb
      ,entries_count
      ,entries_in_use_count
FROM   sys.dm_os_memory_cache_counters
WHERE  TYPE = 'CACHESTORE_SESSION_CONTEXT';

But now I would like to know which key-value pairs of SESSION_CONTEXT() was set -- if any.

After doing extensive research on microsoft sites I didn't found anything related.

Thanks in advance.

1 Answer 1

10

I've checked the definition of many context-related system views, functions, and stored procedures, and cannot locate any further details covering session context variables.

For instance, the sys.dm_os_memory_cache_counters dynamic management view referenced in your question has the following definition:

CREATE VIEW sys.dm_os_memory_cache_counters AS
    SELECT *
    FROM OpenRowSet(TABLE SYSMEMORYCACHECOUNTERS)

The definition of SYSMEMORYCACHECOUNTERS system table is not visible directly, even from the dedicated administrator connection, or DAC, which is therefore a dead-end.

I used this query to look for context-related system objects:

;WITH types AS
(
    SELECT v.xType
        , v.TypeDescription
    FROM (VALUES 
          ('AF', 'Aggregate function (CLR)')
        , ('C', 'CHECK constraint')
        , ('D', 'Default or DEFAULT constraint')
        , ('F', 'FOREIGN KEY constraint')
        , ('L', 'Log')
        , ('FN', 'Scalar function')
        , ('FS', 'Assembly (CLR) scalar-function')
        , ('FT', 'Assembly (CLR) table-valued function')
        , ('IF', 'In-lined table-function')
        , ('IT', 'Internal table')
        , ('P', 'Stored procedure')
        , ('PC', 'Assembly (CLR) stored-procedure')
        , ('PK', 'PRIMARY KEY constraint (type is K)')
        , ('RF', 'Replication filter stored procedure')
        , ('S', 'System table')
        , ('SN', 'Synonym')
        , ('SQ', 'Service queue')
        , ('TA', 'Assembly (CLR) DML trigger')
        , ('TF', 'Table function')
        , ('TR', 'SQL DML Trigger')
        , ('TT', 'Table type')
        , ('U', 'User table')
        , ('UQ', 'UNIQUE constraint (type is K)')
        , ('V', 'View')
        , ('X', 'Extended stored procedure')
    )v(xType,TypeDescription)
)
, sc AS
(
    SELECT so.id
        , ColumnList = STUFF(((SELECT ', ' + sc.name FROM sys.syscolumns sc WHERE sc.id = so.id FOR XML PATH(''))), 1, 2, '')
    FROM sys.sysobjects so
)
SELECT t.TypeDescription
    , so.name
    --, sc.ColumnList
FROM sys.sysobjects so
    LEFT JOIN sc ON so.id = sc.id
    LEFT JOIN types t ON so.xtype = t.xType
WHERE so.name LIKE '%context%'
    OR sc.ColumnList LIKE '%context%'
ORDER BY t.TypeDescription
    , so.name;

in SQL Server 2016, the result of the query is:

╔═══════════════════════════╦════════════════════════════════════════╗
║ Extended stored procedure ║        sp_reset_session_context        ║
╠═══════════════════════════╬════════════════════════════════════════╣
║ Extended stored procedure ║ sp_set_session_context                 ║
║ Extended stored procedure ║ sp_try_set_session_context             ║
║ In-lined table-function   ║ dm_exec_cursors                        ║
║ In-lined table-function   ║ fn_dblog                               ║
║ In-lined table-function   ║ fn_dblog_xtp                           ║
║ In-lined table-function   ║ fn_dump_dblog                          ║
║ In-lined table-function   ║ fn_dump_dblog_xtp                      ║
║ Internal table            ║ plan_persist_context_settings          ║
║ Internal table            ║ plan_persist_query                     ║
║ Stored procedure          ║ sp_MSadd_repl_error                    ║
║ Stored procedure          ║ sp_MSsetcontext_bypasswholeddleventbit ║
║ Stored procedure          ║ sp_MSsetcontext_replagent              ║
║ Stored procedure          ║ sp_sqlagent_verify_database_context    ║
║ View                      ║ dm_db_task_space_usage                 ║
║ View                      ║ dm_exec_query_stats                    ║
║ View                      ║ dm_exec_requests                       ║
║ View                      ║ dm_exec_sessions                       ║
║ View                      ║ dm_filestream_file_io_handles          ║
║ View                      ║ dm_filestream_file_io_requests         ║
║ View                      ║ dm_filestream_non_transacted_handles   ║
║ View                      ║ dm_os_memory_cache_entries             ║
║ View                      ║ dm_os_schedulers                       ║
║ View                      ║ dm_os_tasks                            ║
║ View                      ║ dm_os_threads                          ║
║ View                      ║ dm_os_waiting_tasks                    ║
║ View                      ║ dm_os_workers                          ║
║ View                      ║ dm_tran_locks                          ║
║ View                      ║ query_context_settings                 ║
║ View                      ║ query_store_query                      ║
║ View                      ║ sysprocesses                           ║
╚═══════════════════════════╩════════════════════════════════════════╝
  • Extended stored procedures are implemented in various SQL Server DLLs, and are not easily viewable without reverse-engineering; I've considered them to be black boxes for the purpose of this question.

  • sys.dm_exec_cursors, while pretty clearly not directly related to the "context" we're interested in, has the following definition:

    CREATE FUNCTION sys.dm_exec_cursors (@spid int)
    RETURNS table
    AS
        RETURN SELECT *
        FROM OpenRowSet(TABLE DM_EXEC_CURSORS, @spid)
    

    As you can see above, this function is really just a wrapper around the system-table DM_EXEC_CURSORS; the definition of system tables like this one are again implemented inside the SQL Server engine and not easily inspected.

    The other in-lined table-functions listed in the output of the query above all utilize the same OpenRowSet(TABLE ...) functionality.

  • The sys.sp_MSsetcontext_bypasswholeddleventbit looks intriguing:

    create procedure sys.sp_MSsetcontext_bypasswholeddleventbit @onoff bit -- 1 to turn on
    as
    begin
        declare @cur_context varbinary(128)
        declare @cur_context_first_byte binary(1)
        declare @bitmask tinyint
        declare @retcode int
    
        /*
        ** Security Check
        */
        EXEC @retcode = sys.sp_MSreplcheck_subscribe_withddladmin
        IF @@ERROR <> 0 or @retcode <> 0
            return (1)
    
        -- bit to set: snapshot=1, logreader=2, distrib=4, merge=8, 
        -- replication_agent=16, merge_identityrange_alterconstraint=32
        -- merge_bypasswholeddleventbit=64
        if @onoff=1
            set @bitmask=64
        else
            set @bitmask=255-64
    
        -- get the current context_info. remember we only want to modify a bit without changing the rest of the info
        select @cur_context = isnull(context_info(),0x00)
    
        -- get the first byte out. the replication agent flags are set in the first byte.
        select @cur_context_first_byte = substring(@cur_context, 1, 1)
        -- set the appropriate bit in this one byte (leaving other bits unchanged).
        if @onoff=1
            select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) | @bitmask)
        else
            select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) & @bitmask)
    
        -- replace the first byte of the 128 byte binary variable, so that now it has the appropriate bit set.
        select @cur_context = convert(varbinary(128),stuff (@cur_context, 1, 1, @cur_context_first_byte))
        -- set the context_info again with the new binary(128) value.
        set context_info @cur_context
    
        if @@error <> 0
            return 1
    
        return 0
    end
    

    However, it only uses SET CONTEXT_INFO and CONTEXT_INFO() to inspect and modify the context info. No luck here.

  • Perhaps sys.query_context_settings contains some gold? Nope:

    CREATE VIEW sys.query_context_settings AS
        SELECT
            context_settings_id,
            CONVERT(varbinary(8), set_options) AS 'set_options',
            language_id,
            date_format,
            date_first,
            CONVERT(varbinary(2), status) AS 'status',
            required_cursor_options,
            acceptable_cursor_options,
            merge_action_type,
            default_schema_id,
            is_replication_specific,
            CONVERT(varbinary(1), status2) AS 'is_contained'
        FROM (
            SELECT * FROM sys.plan_persist_context_settings
            UNION ALL
            SELECT TOP 0 * FROM OpenRowSet(TABLE QUERY_STORE_CONTEXT_SETTINGS)
        ) AS ContextSettings
    

I also checked the definitions of SQL modules, such as stored procedures, functions, etc, with this query:

SELECT so.name
    , asm.definition
FROM sys.all_sql_modules asm
    INNER JOIN sys.sysobjects so ON asm.object_id = so.id
WHERE asm.definition LIKE '%context%'
ORDER BY so.name;

None of the items listed in that query contain anything relevant, either.

In short, I could find nothing related to session context. I expect this is by design since the session context could easily be used to store sensitive data for the duration of a session.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.