6

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?

Fabian Schmied
  • 3,885
  • 3
  • 30
  • 49

2 Answers2

7

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.
WaitingForGuacamole
  • 3,744
  • 1
  • 8
  • 22
  • Thanks for the great explanation. As the users in question already have the `Reader` built-in role on the respective Azure SQL resource and roles are additive, I only need to include _additional_ permissions in the custom role, right? I.e., I'd start by just trying the Query Store permissions, test if Query Performance Insights works better than before, then repeat - right? – Fabian Schmied Mar 07 '21 at 19:08
  • That is exactly the approach I'd take. – WaitingForGuacamole Mar 07 '21 at 19:11
2

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.

Fabian Schmied
  • 3,885
  • 3
  • 30
  • 49