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

user_62838 0
Viewed 797

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?

azure-rbac azure-sql-database azure
2 Answers

I think you will need to create an Azure Custom Role, as described in https://docs.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.
user_142896540

Adding to @WaitingForGuacamole's answer: We ended up creating a custom role definition containing Microsoft.Sql/servers/databases/queryStore/write and Microsoft.Sql/servers/databases/topQueries/queryText/action. Assigning this custom role definition to team members who already had the Reader role on the Azure SQL Server then allowed them to view the query texts in Query Performance Insight.

The actual two permissions were provided to us by a very helpful Azure support engineer. YMMV, it might also work with just Microsoft.Sql/servers/databases/topQueries/queryText/action (in addition to the read permissions), as indicated by an Azure Docs Github issue. However, the support engineer was positive we'd also need the Microsoft.Sql/servers/databases/queryStore/write one and we didn't follow up by researching why.

user_628380
Related