Tagging Queries With SESSION_CONTEXT to Trace App Users

Every application request uses the same login, so the database log cannot identify the application user. SESSION_CONTEXT lets the application attach useful connection-local tags. Read those tags in audit logic, while keeping them separate from the permissions and identity guarantees supplied by authentication.

Set SESSION_CONTEXT at the Connection Boundary

SQL Server 2016 introduced SESSION_CONTEXT and sp_set_session_context. The setter associates a named key with a sql_variant value in the session. The reader retrieves that value by key. Multiple named tags are easier to maintain than packing unrelated fields into one binary payload.

I set the application identity and request area when the application obtains its connection. That keeps the audit context attached to the request's actual database route. A tag set on a different connection does not follow the request automatically.

The following values are synthetic demonstration tags. They are not evidence about a real user. Do not store secrets in context simply because it is convenient. Tags should contain the minimum information needed for tracing. Also validate the source and length according to the application contract. A shared login identifies the connection account. It does not magically learn which browser tab asked the question.

EXEC sys.sp_set_session_context @key=N'app_user',@value=N'DemoActor';
EXEC sys.sp_set_session_context @key=N'page',@value=N'Orders';
SELECT SESSION_CONTEXT(N'app_user') AS AppUser,SESSION_CONTEXT(N'page') AS RequestPage;

Read SESSION_CONTEXT Through Audit Defaults

A default expression can read the context when a row is inserted. The next table uses explicit conversions from sql_variant to the intended audit-column types. Defaults apply when the INSERT omits those columns; they do not override values a caller explicitly supplies.

Run the sample in a disposable database with unused object names. The table is a demonstration of tag propagation, not a complete tamper-resistant audit system. Permissions, protected write paths, and the application's trust boundary determine whether a caller can forge audit information.

I keep the tag reader and its destination type visible in the definition. Silent truncation or mismatched types can make useful context unreliable. Decide what should happen when the context is absent. A nullable audit column can preserve that absence, while a required context rule should reject the operation deliberately. Do not turn a missing tag into a plausible default person and call the audit complete.

CREATE TABLE dbo.ContextAuditDemo
(AuditID int IDENTITY PRIMARY KEY,ActionText nvarchar(100),
 AppUser nvarchar(128) DEFAULT CONVERT(nvarchar(128),SESSION_CONTEXT(N'app_user')),
 RequestPage nvarchar(128) DEFAULT CONVERT(nvarchar(128),SESSION_CONTEXT(N'page')));
INSERT dbo.ContextAuditDemo(ActionText) VALUES(N'Demonstration action');
SELECT * FROM dbo.ContextAuditDemo;

Use the Same Reader Inside a Procedure

Procedures can read the current connection's context without adding a parameter to every internal call. That is useful for diagnostic tags and consistent audit enrichment. Keep actual business inputs explicit when hiding them in context would make the procedure contract unclear.

The next procedure reads the two tags. CREATE PROCEDURE begins its own batch, and GO separates the test call. That batch boundary is required even though the procedure is small. Execute the test in the same connection that set the tags.

Which values are optional diagnostics, and which values are required to perform the operation? Document that distinction. A procedure using connection state needs tests for missing tags and unexpected types as well as the normal path. The context belongs to the session, so a procedure called through another connection can legitimately see different values. That behavior should be visible to callers rather than discovered through inconsistent audit rows.

CREATE PROCEDURE dbo.ContextReadDemo
AS
BEGIN
 SET NOCOUNT ON;
 SELECT SESSION_CONTEXT(N'app_user') AS AppUser,SESSION_CONTEXT(N'page') AS RequestPage;
END;
GO
EXEC dbo.ContextReadDemo;

Validate Required Context in Trigger Logic

A trigger can read the session tags during the triggering operation. The next example refuses inserts when the application-user tag is absent. It is a deliberately small guard showing the access pattern. A production trigger needs the broader transaction and audit design reviewed separately.

The tag is connection-level context, not one value per row in a multirow insert. Do not write trigger logic that assumes only one row appears in inserted. If the rows themselves require distinct actors, that information must be modeled explicitly instead of borrowed from one shared session value.

Untrusted callers can set their own context values unless the surrounding design controls the write path. Read-only context prevents later changes in that logical connection but does not certify the original setter's claim. Keep authentication, authorization, and contextual attribution separate. A trigger that checks a tag's presence proves presence, not that the tag names the authenticated person who should own the action.

CREATE TRIGGER dbo.ContextAuditGuard ON dbo.ContextAuditDemo AFTER INSERT
AS
BEGIN
 SET NOCOUNT ON;
 IF SESSION_CONTEXT(N'app_user') IS NULL
 THROW 51001,'Application context is required for this action.',1;
END;
GO

Lock Stable Values and Understand Pooling

@read_only equal to one prevents changing the key again within the relevant logical connection. Set it only after the trusted application context is established. The example locks the demonstrated actor key while leaving the page tag available for the designed request lifecycle.

Pooled connections need correct reset and checkout behavior. Set the required tags every time the application takes a connection from the pool, and confirm how the client resets session state between logical uses. Do not assume an earlier actor's tag is appropriate for a later request.

The older CONTEXT_INFO stores a binary payload with a much smaller fixed capacity and no named-key structure. The following comparison shows its basic setter and reader. Keep the representation contract explicit if an existing application uses it. Named tags improve organization, but moving an old payload requires deliberate decoding and migration rather than copying arbitrary bytes into a new named key.

EXEC sys.sp_set_session_context @key=N'app_user',@value=N'DemoActor',@read_only=1;
DECLARE @LegacyTag varbinary(128)=CONVERT(varbinary(128),N'DemoActor');
SET CONTEXT_INFO @LegacyTag;
SELECT SESSION_CONTEXT(N'app_user') AS NamedContext,CONTEXT_INFO() AS LegacyContext;

Test the Complete Connection Lifecycle

Test a fresh connection, missing context, a readonly key, pooled reuse, and the application's normal exception path. Confirm the audit values belong to the request that actually performed the work. Closing one query window and opening another is also a useful demonstration of session-local scope.

Keep the demonstration objects in the disposable database and remove them after validation. For a real audit design, review permissions on direct table writes and the trusted procedure path. A convenient default does not replace that review.

SESSION_CONTEXT is useful for tracing shared-login application work when the connection lifecycle and trust boundary are explicit. Set the tags at checkout, read them consistently, and distinguish attribution from authentication. The final audit should explain both the database account and the application context under which the operation occurred.

Related reading on this blog: SQL Server Audit: Tracking Who Read or Changed Sensitive Tables and Who Changed This Row? Auditing Data Changes.

A session tag is not authentication, it is application context that still needs a trusted source.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.