Injecting connection context information using a persistent table

I am working on an application that has several legacy modules that rely heavily on stored procedure (no ORM, so all fetches and data persistence is done through stored procedures).

Security for legacy modules relies on SUSER_NAME() to get current user and apply security rules.

I am migrating it to use an ORM (Entity Framework) and SQL connector will use a generic user to connect to the database (SQL Server), so I have to provide current username to many procedures.

In order to avoid changes in .NET code, I thought of "injecting" somehow current user in the context when a new connection is made:

CREATE TABLE dbo.ConnectionContextInfo 
(
    ConnectionContextInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ConnectionContextInfo PRIMARY KEY,
    Created DATETIME2 NOT NULL CONSTRAINT DF_ConnectionContextInfo DEFAULT(GETDATE()),
    SPID INT NOT NULL,
    AttributeName VARCHAR(32) NOT NULL, 
    AttributeValue VARCHAR(250) NULL,
    CONSTRAINT UQ_ConnectionContextInfo_Info UNIQUE(SPID, AttributeName)
)
GO

When a connection is opened (or reused, as a connection pool is used), the following command is used:

exec sp_executesql N'
    DELETE FROM dbo.ConnectionContextInfo WHERE SPID = @@SPID AND AttributeName = @UsernameAttribute;
    INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName, AttributeValue) VALUES (@@SPID, @UsernameAttribute, @Username);
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
go

(0 CPU, ~15 reads, <6 ms)

A scalar function allows to easily get current user:

alter FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
    DECLARE @ret VARCHAR(250) = (SELECT AttributeValue FROM ConnectionContextInfo where SPID = @@SPID AND AttributeName = 'Username')
    -- fallback to session current, if no data is found on current SPID (i.e. call outside of the actual application)
    RETURN ISNULL(@ret, SUSER_NAME())
END
GO

Are there any caveats (robu

In terms of performance, you will incur the overhead of the DELETE and INSERT every time the connection is opened. Alternatively, you could use the built-in connection CONTEXT_INFO for this purpose. The example below stores the information in a fixed-length 48-byte structure.

EXEC sp_executesql N'
    DECLARE @ContextInfo binary(48);
    SET @ContextInfo = CAST(CAST(@UsernameAttribute AS nchar(8)) + CAST(@Username AS nchar(16)) AS binary(48));
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
GO

CREATE FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE
      @ContextInfo binary(48) = CONTEXT_INFO()
    , @Username nvarchar(16);
    SET @Username = RTRIM(CAST(SUBSTRING(@ContextInfo, 17, 32) AS nvarchar(16)));

    RETURN ISNULL(@Username, SUSER_NAME());

END
GO

Also, sp_set_session_context and SESSION_CONTEXT() are available in SQL Server 2016 and Azure SQL Database. That's a much cleaner method if available to you.

StackExchange Database Administrators Q#134890, answer score: 5