Search code examples
azureazure-sql-databaseazure-rbac

Azure SQL Permissions: How to allow using Query Performance Insight, but not changing settings such as the pricing tier?

I would like to give our team members the necessary permissions to use the Query Performance Insight feature for an Azure SQL database, including the possibility to see the query text of long-running queries.

They already have "Reader" and "Monitoring Contributor" roles, so they can access the Query Performance Insight feature in the Azure Portal and see the IDs of long-running queries. However, when they click on a long-running query, they cannot see the query text. An error is shown indicating that "The connection timed out while running the query".

If I assigned them the "SQL DB Contributor" role, they would be able to use that feature, but they could then also change database settings such as the pricing tier, which I do not want.

Is there a role assignment that does what I need?

Solution

  • I think you will need to create an Azure Custom Role, as described in https://learn.microsoft.com/en-us/azure/role-based-access-control/custom-roles .

    You can start with Reader, and then include permissions you want, or start with SQL DB Contributor, and remove permissions you don't want. This will require experimentation.

    From your subscription, create a new Custom Role:

    New Custom Role

    Then from that role, you will add or exclude permissions:

    Add/Exclude permissions

    Permissions that would be interesting to me would be:

    List Query Store texts - for adding to a Reader

    LQS permission

    and Update Database - for excluding from a DB Contributor

    Update database permission

    Once that's done, you would go to the Access Control blade for the server that contains your database, and then add your users with that new custom role. Test, tweak, repeat until you have the security profile you want. Which role you use as your basis depends upon how close to a least-privilege security model you wish to adopt.

    Edit: One possible way to figure out the permission to assign would be:

    1. Scale the database up
    2. Scale it back down
    3. Go to the resource group, select your database, and Export Template
    4. Inspect the JSON, which will be the ARM that was applied during the operation (you might need to look at multiple deployments to figure this out)
    5. Once you find the operation, the provider in the JSON should give you a clue as to what to exclude from any roles you create.