Create schemas (eg a schema per department)

Create local roles with identical names (eg role procurement for schema procurement).

(Instead of local roles, we can also use Entra Security Groups.)

View [dbo].[Permissions_per_Role_User] shows current permissions per role and user

• - Create schemas CREATE SCHEMA procurement;
• - Create database roles CREATE ROLE procurement;
• - Grant SELECT (read-only) on each schema to its role GRANT SELECT ON SCHEMA::procurement TO procurement;
• - Assign user to a role (or several roles) ALTER ROLE [procurement] ADD MEMBER [[email protected]];

Make sure user does not have access to workspace. If they are even just a “Viewer”, they will see all data in the db.

For dbo, permissions are a little different

CREATE ROLE role_dbo_reader;

GRANT SELECT ON SCHEMA::dbo TO role_dbo_reader;

ALTER ROLE role_dbo_reader ADD MEMBER [[email protected]];

Share database from here