Row-Level Security with Azure AD Groups on Azure SQL Managed Instance

Question

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.

e.g.

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_MEMBER() and IS_ROLEMEMBER() but they seem to only work with normal Windows Domains, not with Azure AD.

Thanks

Answer 1

A user could be a member of multiple security groups that have access to your database. So none of the USER_NAME() family of functions will return the role security principal.

You can find all the active group memberships for a user by querying sys.user_token, or by running IS_ROLEMEMBER for a particular role.

IS_ROLEMEMBER() but they seem to only work with normal Windows Domains, not with Azure AD.

IS_ROLEMEMBER() works for AAD Security Groups so long as you have added that Security Group as a user. EG

create user DepartmentA from external provider

or, for MI

create login DepartmentA from external provider