Share via

Level 2 AD Group & SQL server Microsoft Entra admin group & usage of IS_MEMBER for Row Level Security

Sandeep Kumar 0 Reputation points
2024-09-06T05:57:57.62+00:00

Currently we are running into an issue developing Row Level Security since the IS_MEMBER does not work for the Microsoft Entra Admin group setup for that SQL server. (https://learn.microsoft.com/en-us/sql/t-sql/functions/is-member-transact-sql?view=sql-server-ver16)

 

When the SQL Server is provisioned the Product Parent AD Group APP-XXX-PP-XXXX-DEV gets set as the Microsoft Entra Admin for that server. 

 

So now we can't have users of our system (apps/frontend) also belong to the APP-XXX-PP-XXXX-DEV group otherwise the RLS will fail.

 

Any experiences with this?

 

Azure Role-based access control
Azure Role-based access control
An Azure service that provides fine-grained access management for Azure resources, enabling you to grant users only the rights they need to perform their jobs.
809 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
21,798 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. James Hamil 24,666 Reputation points Microsoft Employee
    2024-09-13T21:12:54.3466667+00:00

    Hi @Sandeep Kumar , try separating the Admin and User roles. Make sure that users who need to access the system (apps/frontend) do not belong to the APP-XXX-PP-XXXX-DEV group.

    You could also try using custom roles. Instead of relying on the IS_MEMBER function, you can create custom roles and use them to manage access control. This should help you avoid conflicts with the Microsoft Entra Admin group.

    Please let me know if this helps.

    Best,

    James

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.