Row level security and physical user creation is it an overhead

The database that I am currently working has RLS implemented as suggested in Microsoft blogs and a few user blogs.

Example: creating a physical user and EXEC AS USER and selecting from tables for the user selected and REVERT

CREATE USER User1 WITHOUT LOGIN;
CREATE USER User2 WITHOUT LOGIN;
CREATE USER User3 WITHOUT LOGIN;

What if we exhaust the limit for physical users (I think it is server_prinicipal_id whose limit is int), what if your application supports millions of users whose data needs to be restricted based on what they can see?

Is creating physical user for every logical user not an overhead?

Is that a best practice?

Is there a performance overhead?

If you suspect that you'll be creating millions of database users, you should probably not do that. I don't know what the maximum number of users for a single database is, but see Michael Swart's excellent article Swart’s Ten Percent Rule for why you don't want to come even close to that maximum.

You have a couple of other options rather than creating database users for each connecting user. See Limiting access to data using Row-Level Security on Microsoft's SQL Server Blog.


Can I limit access based on AD group memberships?


Yes, you can use the IS_MEMBER() function in your predicate to check SQL role or AD group memberships. For an example, see the RLS Hospital Demo script.


What if my application uses connection pooling with a single login for all users?


No problem, your application can use the new SESSION_CONTEXT feature to get and set session-scoped key-value pairs to identify users for RLS, while still enabling efficient connection pooling.

The IS_MEMBER() approach is similar to the common USER_NAME() approach.

The SESSION_CONTEXT approach has a more detailed example in the docs here:

Scenario for users who connect to the database through a middle-tier application

The filter function pulls an application-set user id out of the SESSION_CONTEXT:

CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)  
    RETURNS TABLE  
    WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result  
    WHERE  
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')    
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;   
GO

Which means the application needs to be modified to set that value whenever it connects to SQL Server to execute queries:

EXEC sp_set_session_context @key=N'UserId', @value=1;

StackExchange Database Administrators Q#232839, answer score: 4