How to give Schema Level permission in Azure Data Warehouse (Synapse)?

Question

In our Azure SQL Data Warehouse (Synapse), we have two schemas dbo and staging. I want to give schema level permission to my Azure Active Directory Group users only to 'dbo' schema. The expected behaviour I'm looking for is that after giving this permission, the Azure Active Directory Group users shouldn't see staging schema when opening the database in ssms. I tried the following to create the user and give permissions but still, users see staging schema in their ssms.

1. I created the user and added db_datareader role using the following:

CREATE USER [Azure Active Directory Group] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo;
EXEC sp_addrolemember 'db_datareader', 'Azure Active Directory Group'

2. And then grant schema level permission using:

GRANT SELECT ON SCHEMA::dbo TO [Azure Active Directory Group]

3. The following query returns:

select pr.name,
       pr.type_desc,
       pe.permission_name,
       pe.state_desc,
       pe.class_desc,
       object_name(pe.major_id)

FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id where name='Azure Active Directory Group Users';

Does anybody know how I can give schema level permission? I'm stuck!

Answer 1

db_datareader: Members of the db_datareader fixed database role can read all data from all user tables. It means the db_datareader role can view all the schemas. So we need to revoke this permission.

There are two ways to reslove the problem:

1. Revoke the db_datareader permission and grant the permission to the user.

EXEC sp_droprolemember 'db_datareader', 'Azure Active Directory Group'

GRANT SELECT ON SCHEMA::dbo TO [Azure Active Directory Group]

2. We also can add a custom role and grant the permission to the role.

CREATE ROLE <Role_Name>; 

GRANT SELECT ON schema::dbo TO <Role_Name>;

EXEC sp_addrolemember '<Role_Name>', 'Azure Active Directory Group'