Lesson Learned #135: Row Level Security based on IsRole_Member

This article is contributed. See the original author and article here.

Today, I worked on a service request that our customer needs to avoid that some users could retrieve some rows depending on a specific role. In this situation, we are going to use Is_RoleMember function. 

 

For example, we have an Azure Active Directory group called MyAADGroup that contains all the users that we will have access to the data.

 

1) We are going to create the group in Azure SQL Database, running the following query:

 

 

CREATE USER [MyAADGroup] FROM EXTERNAL PROVIDER

 

 

2) We’re going to create the role, for example, auditors that will assign to the group MyAADGroup, running the following command:

 

sp_addrolemember 'auditors','MyAADGroup'

 

 

3) The next step, would be to create the security function, running the following command:

 

CREATE or alter Function [dbo].[fn_securitypredicateOrder] (@UserName sysname)
returns table
with Schemabinding
as
return select 1 as [fn_securityPredicateOrder_result] where IS_ROLEMEMBER('auditors')=1

 

 

4) Finally, we need to create the security policy running the following command:

 

create security Policy fn_securitydata
add Filter Predicate
dbo.fn_securitypredicateOrder(suser_name())
on MyTableDummy

 

 

Enjoy!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.