row level security - Do we have to really create a physical user - isnt there a performance issue or maintenance over head?

Question

question

Aparna-1 asked Mar 22, '19

row level security - Do we have to really create a physical user - isnt there a performance issue or maintenance over head?

Every implementation of row-level security both in Microsoft or blogs from users discusses a need to create user Example:

CREATE USER Manager WITHOUT LOGIN;

CREATEUSER Sales1 WITHOUT LOGIN;

CREATEUSER Sales2 WITHOUT LOGIN;

Can anyone highlight issues that they have faced with these physical users? What if you 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?

security

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-03-22T10:20:06Z

Kev Riley answered Mar 22, '19

No you don't need to create a SQL user for every logical user.

You can use roles or AD groups to group users together(example: https://www.sqlservercentral.com/Forums/1893314/Row-Level-Security-based-on-membership-in-AD-Group-or-ServerRole), or you can use SESSION_CONTEXT (example: https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-2017#MidTier) to pass user information through a single login in a tiered app.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question