SET NOCOUNT ON

DECLARE @DBName VARCHAR(255)

DECLARE @sqlStm NVARCHAR(500)

CREATE TABLE #dbTable

(

ServerName varchar(200) ,

DBNamevarchar(255) ,

UserNamevarchar(100) ,

RoleNamevarchar(100)

)

DECLARE dbCursor CURSOR FOR

SELECT name AS DbName

FROM sys.databases

WHERE name not in( 'master', 'msdb' , 'tempdb', 'model' )

AND state=0

OPEN dbCursor

FETCH NEXT FROM dbCursor INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

--SET @sqlStm = 'USE ' + @DBName

--Print @DBName

--EXEC (@sqlStm)

--INSERT INTO #dbTable (ServerName , DBName, [UserName], RoleName )

EXECUTE('USE ' + @DBName + '; INSERT INTO #dbTable (ServerName , DBName, [UserName], RoleName )SELECT @@ServerName , db_name(), u.[name] , r.[name]

FROM sys.database_principals u

JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id

JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id')

FETCH NEXT FROM dbCursor INTO @DBName

END

CLOSE dbCursor

DEALLOCATE dbCursor

SELECT * FROM #dbTable

DROP TABLE #dbTable

GO

We have always seen database level access to the end user but this following walk through will help you to understand How to setup Row Level Security(RLS) access for Azure Active Directory Group (AAD Group) for Table in Azure Database.

This entire SQL scripts developed and tested by myself with AAD Group. There are fours section and follow the each step to achieve your goal.

--**********************************************************

--PART 1 Database level User Access RLS AAD

-- End User Added on below 3 AAD Groups

--**********************************************************

--Group Name: AZ-AS-GRP1

--Group Name: AZ-AS-GRP2

--Group Name: AZ-AS-GRP3

-- STEP 1 Create User

CREATE USER [AZ-AS-GRP1] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]

GO

CREATE USER [AZ-AS-GRP2] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]

GO

CREATE USER [AZ-AS-GRP3] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]

GO

-- STEP 2 Database Role

CREATE ROLE db_RLS_DataReader AUTHORIZATION [dbo]

GO

--STEP 3 Grant select permission to Database Role

GRANT SELECT ON OBJECT::RPT.DIM_PRODUCT_SALES TO [db_RLS_DataReader]

-- STEP 4 Add an existing user to the role

ALTER ROLE [db_RLS_DataReader] add member [AZ-AS-GRP1]

ALTER ROLE [db_RLS_DataReader] add member [AZ-AS-GRP2]

ALTER ROLE [db_RLS_DataReader] add member [AZ-AS-GRP3]

--================================================

-- Validate the Role

SELECT

members.name as 'members_name',

roles.name as 'roles_name',

roles.type_desc as 'roles_desc' ,

members.type_desc as 'members_desc'

FROM sys.database_role_members rolemem INNER JOIN sys.database_principals roles

ON rolemem.role_principal_id = roles.principal_id

INNER JOIN sys.database_principals members ON rolemem.member_principal_id = members.principal_id

WHERE roles.name ='db_RLS_DataReader'

--**********************************************************

--PART 2 Row Level User Access Table

--**********************************************************

-- STEP 5 RLS User Access Table

CREATE TABLE [RPT].[DIM_DATA_USER_ACCESS](

[ID] [int] IDENTITY(1,1) NOT NULL ,

[Assign_Nm] [varchar](20) NULL,

[AAD_Group_Name] [nvarchar] (120) NULL,

[User_Email_Id] [varchar] (60) NULL,

[Is_Active][bit] default(1) NULL,

[Deletion_DT][datetime] NULL, -- 1 Active , 0 InActive

[Created_By] [varchar](50) DEFAULT suser_sname() NULL,

[Created_Date] [datetime] DEFAULT getdate() NULL,

[Modified_By] [varchar](50) DEFAULT suser_sname() NULL,

[Modified_Date] [datetime] DEFAULT getdate() NULL,

CONSTRAINT [PK_ID2] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

-- STEP 6: Create the specified users

CREATE USER FULL_DATA WITHOUT LOGIN;

GO

CREATE USER SALES_DATA1 WITHOUT LOGIN;

GO

CREATE USER IT_DATA2 WITHOUT LOGIN;

GO

CREATE USER PRO_DATA3 WITHOUT LOGIN;

GO

-- INSERT DATA

SELECT * FROM [RPT].[DIM_DATA_USER_ACCESS] where User_Email_Id =user_name()

INSERT INTO [RPT].[DIM_DATA_USER_ACCESS] ( [ID], [Assign_Nm], [AAD_Group_Name] , [User_Email_Id])

VALUES (1,'SALES_DATA1' , 'AAD1', 'MyUser1@abc.com' )

INSERT INTO [RPT].[DIM_DATA_USER_ACCESS] ( [ID], [Assign_Nm], [AAD_Group_Name] , [User_Email_Id])

VALUES (2,'IT_DATA2', 'AAD2', 'MyUser2@abc.com' )

INSERT INTO [RPT].[DIM_DATA_USER_ACCESS] ( [ID], [Assign_Nm], [AAD_Group_Name] , [User_Email_Id])

VALUES (3,'PRO_DATA3', 'AAD3', 'MyUser3@abc.com' )

-- STEP 7 Grant Access to Specified User

GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS] TO FULL_DATA;

GRANT UPDATE ON [RPT].[DIM_DATA_USER_ACCESS] TO FULL_DATA;

GRANT DELETE ON [RPT].[DIM_DATA_USER_ACCESS] TO FULL_DATA;

GRANT INSERT ON [RPT].[DIM_DATA_USER_ACCESS] TO FULL_DATA;

GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS] TO [AZ-AS-GRP1]

GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS] TO [AZ-AS-GRP2]

GRANT SELECT ON [RPT].[DIM_DATA_USER_ACCESS] TO [AZ-AS-GRP3]

--**********************************************************

--PART 3 Security and Functions

--**********************************************************

-- STEP 8: Create the inline table-valued function

-- DROP FUNCTION [dbo].[fn_MY_Security]

--GO

CREATE FUNCTION dbo.fn_MY_Security

(@MAPPING_NAME NVARCHAR (255) ) --, @USER_NAME NVARCHAR (255)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN

SELECT 1 AS fn_MY_Security_Result

WHERE @MAPPING_NAME IN

( SELECT DISTINCT DH.[Assign_Nm]

FROM [RPT].[DIM_MY_DEPT] AS DH

INNER JOIN [RPT].[DIM_DATA_USER_ACCESS] AS UA

ON DH.[Assign_Nm] = UA.[Assign_Nm]

WHERE (LOWER( [User_Email_Id]) = LOWER( USER_NAME() ) -- Specific User

AND [Is_Active]=1) OR LOWER( USER_NAME() ) = 'FULL_DATA' -- Admin User

)

GO

-- STEP 9: Apply the Security Policy

-- DROP SECURITY POLICY [dbo].[MY_UserFilter]

-- GO

CREATE SECURITY POLICY [dbo].[MY_UserFilter]

ADD FILTER PREDICATE [dbo].fn_MY_Security([Assign_Nm])

ON [RPT].[DIM_DATA_USER_ACCESS]

WITH (STATE = ON);

GO

--**********************************************************

--PART 4 Data Validation

--**********************************************************

-- -- STEP 10 Data Validation

-- All data for Admin RLS

EXECUTE AS USER = 'FULL_DATA';

SELECT * FROM [RPT].[DIM_DATA_USER_ACCESS]

WHERE AAD_Group_Name LIKE 'AZ%'

REVERT;

GO

EXECUTE AS USER = 'FULL_DATA';

SELECT * FROM [RPT].[DIM_DATA_USER_ACCESS]

WHERE AAD_Group_Name LIKE 'AZ%'

ORDER BY 2

REVERT;

GO

--Specific users

EXECUTE AS USER = 'SALES_DATA1';

SELECT * FROM [RPT].[DIM_DATA_USER_ACCESS]

REVERT;

GO

EXECUTE AS USER = 'IT_DATA2';

SELECT * FROM [RPT].[DIM_DATA_USER_ACCESS]

REVERT;

GO

EXECUTE AS USER = 'PRO_DATA3';

SELECT * FROM [RPT].[DIM_DATA_USER_ACCESS]

REVERT;

GO

--This catalog view returns all the Security Policies in the database

--Execute the following statement to get all the security policies in the database with important security policy attributes/columns

SELECT Name, object_id, type, type_desc,is_ms_shipped,is_enabled,is_schema_bound

FROM sys.security_policies

--This catalog view returns all the Security Predicates in the database

SELECT *

FROM sys.security_predicates