SQL Server SESSION_CONTEXT function - SQL Server 2016 Enhancements

If you are a web developer and have worked with Classic ASP or ASP.Net, then you know that how important it is for maintaining Session Variables for keeping various values throughout the session. Although similar capability was exist with SQL Server, it was not exactly functioning as Session Variables as it had many limitations. Now, with SQL Server 2016, a new function has been introduced, called SESSION_CONTEXT that accepts a key of key-value pair set with sp_set_session_context and returns the value of submitted key.

The previous versions before 2016, this was implemented via CONTEXT_INFO function. This holds only a single binary value, making it difficult to maintain multiple values. It is limited to 128 bytes per connection, which is not enough at all and cannot be secured because user can change it if want. With these limitations, it was not used with much implementations.

Values for sessions are set with sp_set_session_context that accepts two mandatory parameters and one optional. First parameter the key (or name) of the session variable which type is sysname. Second parameter is the value. It is accepted as sql_variant and value can be as large as 256KB. Third parameter which is optional that indicates whether the variable is read-only or not. The default is 0 which is not read-only.

Let's test this. The following code creates;

1. Create a Login called Smith for testing
2. Create a Database called Config. Assume that you maintain additional configuration details in this database for your applications.
3. Add Smith as a User to the database. He will be a member of db_datareader role.
4. Create a Table called EmployeeSecurityLevel and inserts two records.
5. Create a Procedure called GetProducts for testing. This procedure checks the set Security Level and displays. In addition to that, Smith is added to the database.
6. Create a Logon Trigger to capture user and set Security Level as a Session Variable. It assigned the relevant Security Level taken from Config database to a variable called CompanySecurityLevel.

view plainprint?

1. -- ******** 1 ********  
2. -- Create login for Smith  
3. USE master;  
4. GO  
5. CREATE LOGIN Smith WITH PASSWORD = 'Pa$$w0rd';  
6. GO  
7.   
8. -- ******** 2 ********  
9. -- Create a Config database  
10. CREATE DATABASE Config;  
11. GO  
12.   
13. USE Config;  
14. GO  
15.   
16. -- ******** 3 ********  
17. -- Add Smith to Config  
18. CREATE USER Smith FOR LOGIN Smith;  
19. EXEC sp_addrolemember 'db_datareader', 'Smith';  
20.   
21. -- ******** 4 ********  
22. -- Create a table for holding security levels  
23. CREATE TABLE dbo.EmployeeSecurityLevel  
24. (  
25.  Id int Identity(1,1) PRIMARY KEY  
26.  , UserId nvarchar(200) NOT NULL INDEX IX_EmployeeSecurityLevel_UserId NONCLUSTERED  
27.  , SecurityLevel tinyint NOT NULL  
28. );  
29.   
30. -- Add two users  
31. INSERT INTO dbo.EmployeeSecurityLevel  
32.  (UserId, SecurityLevel)  
33. VALUES  
34.  (N'Dinesh', 1)  
35.  , (N'Smith', 3);  
36.   
37.    
38.   
39. -- ******** 5 ********  
40. USE AdventureWorks2016CTP3;  
41. GO  
42.   
43. -- Add SP for getting products  
44. DROP PROC IF EXISTS dbo.GetProducts;  
45. GO  
46. CREATE PROC dbo.GetProducts  
47. AS  
48. BEGIN  
49.   
50.  DECLARE @SecurityLevel tinyint  
51.  SELECT @SecurityLevel = Convert(tinyint, SESSION_CONTEXT(N'CompanySecurityLevel'));  
52.   
53.  PRINT 'Displaying products as per security level.....'  
54.  PRINT 'Security level: ' + Convert(varchar(3), @SecurityLevel);  
55. END;  
56.   
57. -- Add Smith and set relevant permission  
58. CREATE USER Smith FOR LOGIN Smith;  
59. EXEC sp_addrolemember 'db_datareader', 'Smith';  
60. GRANT EXECUTE ON dbo.GetProducts TO Smith;  
61.   
62. -- ******** 6 ********  
63. USE master;  
64. GO  
65.   
66. -- Creating Logon trigger to set the session variable  
67. CREATE TRIGGER SetSessionVariables  
68. ON ALL SERVER   
69. AFTER LOGON  
70. AS  
71. BEGIN  
72.  DECLARE @SecurityLevel tinyint  
73.   
74.  -- Query Config database and get the value  
75.  SELECT @SecurityLevel = SecurityLevel  
76.  FROM Config.dbo.EmployeeSecurityLevel  
77.  WHERE UserId = CURRENT_USER;  
78.   
79.  -- Set the value, note that it is set as read-only  
80.  EXEC sp_set_session_context N'CompanySecurityLevel', @SecurityLevel, 1;   
81. END;  

-- ******** 1 ********
-- Create login for Smith
USE master;
GO
CREATE LOGIN Smith WITH PASSWORD = 'Pa$$w0rd';
GO

-- ******** 2 ********
-- Create a Config database
CREATE DATABASE Config;
GO

USE Config;
GO

-- ******** 3 ********
-- Add Smith to Config
CREATE USER Smith FOR LOGIN Smith;
EXEC sp_addrolemember 'db_datareader', 'Smith';

-- ******** 4 ********
-- Create a table for holding security levels
CREATE TABLE dbo.EmployeeSecurityLevel
(
 Id int Identity(1,1) PRIMARY KEY
 , UserId nvarchar(200) NOT NULL INDEX IX_EmployeeSecurityLevel_UserId NONCLUSTERED
 , SecurityLevel tinyint NOT NULL
);

-- Add two users
INSERT INTO dbo.EmployeeSecurityLevel
 (UserId, SecurityLevel)
VALUES
 (N'Dinesh', 1)
 , (N'Smith', 3);

 

-- ******** 5 ********
USE AdventureWorks2016CTP3;
GO

-- Add SP for getting products
DROP PROC IF EXISTS dbo.GetProducts;
GO
CREATE PROC dbo.GetProducts
AS
BEGIN

 DECLARE @SecurityLevel tinyint
 SELECT @SecurityLevel = Convert(tinyint, SESSION_CONTEXT(N'CompanySecurityLevel'));

 PRINT 'Displaying products as per security level.....'
 PRINT 'Security level: ' + Convert(varchar(3), @SecurityLevel);
END;

-- Add Smith and set relevant permission
CREATE USER Smith FOR LOGIN Smith;
EXEC sp_addrolemember 'db_datareader', 'Smith';
GRANT EXECUTE ON dbo.GetProducts TO Smith;

-- ******** 6 ********
USE master;
GO

-- Creating Logon trigger to set the session variable
CREATE TRIGGER SetSessionVariables
ON ALL SERVER 
AFTER LOGON
AS
BEGIN
 DECLARE @SecurityLevel tinyint

 -- Query Config database and get the value
 SELECT @SecurityLevel = SecurityLevel
 FROM Config.dbo.EmployeeSecurityLevel
 WHERE UserId = CURRENT_USER;

 -- Set the value, note that it is set as read-only
 EXEC sp_set_session_context N'CompanySecurityLevel', @SecurityLevel, 1; 
END;

Once everything is set, when Smith logs in, his Security Level is captured via the trigger and set with session_context. This value can be read in any database within his session. Below code shows that Smith executes the procedure and result is based on his Security Level.

view plainprint?

1. -- Logged in as Smith  
2. USE AdventureWorks2016CTP3;  
3. GO  
4. -- Smith executing the SP  
5. EXEC dbo.GetProducts;  

-- Logged in as Smith
USE AdventureWorks2016CTP3;
GO
-- Smith executing the SP
EXEC dbo.GetProducts;