To implement this efficiently while minimizing administrative overhead as you scale, you should combine Row-Level Security (RLS) with Microsoft Entra groups.
Why this scales:
Zero SQL Changes: When a new department is added, you simply create a new Entra group and add a new row to your data with that group name. You do not need to update the SQL function or policy.
Centralized Management: Adding or removing employees happens in Microsoft Entra ID, automatically updating their access in SQL without DBA intervention.
Performance: The predicate is evaluated at the storage engine level, ensuring users only "see" the rows they have access to, which also prevents accidental data leakage in logs or execution plans.
Note:
Here is the most scalable way to structure this:
1. Group Strategy
Instead of mapping individual users to departments in your SQL code, create a Microsoft Entra ID Security Group for each department. Users should be added to these groups at the identity provider level, not within the database.
2. The Filter Predicate
Create a logic function that checks the user's group membership. The most efficient method is using the IS_MEMBER() function or by mapping the SESSION_USER to a mapping table.
CREATE FUNCTION Security.fn_departmentSecurityPredicate(@Department AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_security_predicate_result WHERE
-- Allows a user to see rows where their Entra Group matches the department column IS_MEMBER(@Department) = 1 OR IS_MEMBER('DatabaseAdmins') = 1; -- Example exception for IT/Admins
3. Apply the Security Policy
Apply the function to your target table. By using STATE = ON, the filter is automatically applied to all incoming queries.
CREATE SECURITY POLICY DepartmentFilter
ADD FILTER PREDICATE Security.fn_departmentSecurityPredicate(DepartmentColumn) ON dbo.YourTableName WITH (STATE = ON); Reference:
https://medium.com/@aakanksha.s0905/implementing-row-level-security-rls-with-azure-active-directory-aad-groups-in-sql-views-52479135d91a

Explanation:
Operating System: Ubuntu Linux
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/linux/sql-server-iaas-agent- extension-linux?view=azuresql&tabs=azure-powershell#limitations Limitations: The Linux SQL IaaS Agent extension has the following limitations:
Only SQL Server VMs running on the Ubuntu Linux operating system are supported. Other Linux distributions are not currently supported.
Benefit: Registration of a SQL virtual machine resource in Azure
In below link, automated backups and patching are NOT present in the Benefits list for Linux:
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/linux/sql-server-iaas-agent- extension-linux?view=azuresql&tabs=azure-powershell

For more throughput, you can add additional data disks and use disk striping.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/storage- configuration?tabs=windows2016