I have an Azure SQL Managed Instance on which I need to implement Row-Level Security on some tables to filter the data queried by some users.
All my users authenticate with Azure Active Directory authentication and they are allowed access to the database based on their membership to specific AAD Groups that are configured on my SQL Instance. (i.e. In SQL I don't have any single user configured for login or database access or select permissions, only AAD groups).
I'm trying to find a way to implement Row-Level Security using the same model. So I'd like to implement it by referencing the specific AAD Group for which I need to filter out rows in my tables.
User Bob belongs to AAD group DepartmentA.
DepartmentA group is enabled on my SQL Managed Instance to login and was granted
SELECT permission on all tables in database DatabaseA
I want to filter out rows with Row-Level Security in TableA by writing a policy that let all users in my DepartmentA group see only records related to their department (just assume that department name is written on a table column).
Is there any way to implement this?
I found functions like
IS_ROLEMEMBER() but they seem to only work with normal Windows Domains, not with Azure AD.