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:






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
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
on MyTableDummy




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

%d bloggers like this: