How to create custom role that only has access to one schema in a dedicated sql pool

Question

I have a Dedicated SQL Pool, and I'm trying to create a bunch of roles that are assigned to different groups where each group will only have access to one schema in the entire SQL Pool. Read access only.

{
    "id": "####",
    "properties": {
        "roleName": "Schema_1",
        "description": "",
        "assignableScopes": [
            "/subscriptions/####/resourceGroups/####"
        ],
        "permissions": [
            {
                "actions": [
                    "Microsoft.Sql/managedInstances/databases/schemas/read"
                ],
                "notActions": [],
                "dataActions": [],
                "notDataActions": []
            }
        ]
    }
}

How do I specify that I only want to grant one specific schema, let's say that schema is called schema1.

Users in this role should be able to access the SQL Dedicated Pool/Database using their Microsoft AD account and only see that schema1.

Thank you!

Answer 1

Hi @Rita Wang ,

Welcome to the Microsoft Q&A platform and thank you for your query.

Azure Role-Based Access Control (RBAC) [Custom role] is primarily used for managing access to Azure resources, including Azure SQL dedicated Databa. However, RBAC does not manage permissions within the database itself. For example, you can use RBAC to control who can create and manage SQL pools. But to control access to specific schemas within a SQL pool or database, you would typically use SQL permissions.

You can follow the below actions to grant permission to a group for only one schema:

1. Create the group in Azure Active Directory (AAD): If it is not already created, create the group in AAD and add all the AD users to this group. You can follow the instructions provided here- https://learn.microsoft.com/en-us/azure/active-directory/fundamentals/how-to-manage-groups

Create the group in the SQL Pool: Once the group is created in AAD, you can create it in the SQL Pool using the following command:

CREATE USER [Your_AAD_Group_Name] FROM EXTERNAL PROVIDER;

1. Grant SELECT permission to the group: After creating the group, you can grant it SELECT permission on your specific schema (let’s say schema1) using this command:

GRANT SELECT ON SCHEMA::schema1 TO [Your_AAD_Group_Name];

Please replace Your_AAD_Group_Name with the actual name of your AAD group, and schema1 with the actual name of your schema.

By following these steps, the AAD group will have permission only on Schema1, and all users who are part of the group can access this schema.

You can refer to link for the access on Azure Synapse workspace- Please refer to the link

I hope you find this helpful. Let us know in case you have any questions.

Thank you!!

---