0

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

1 Answer 1

0

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
1
  • 1
    Thank you very much for your answer David! I thought that IS_ROLEMEMBER didn't work because I was testing it with a user that has sysadmin role; and I just found out that sysadmins don't access the DB as themselves but they use dbo's permissions, that's why IS_ROLEMEMBER doesn't work on them. Thank you Dec 14, 2021 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.