Read-only permission to view query text

[desmondgc]

Without the ability to view query text, Query Performance Insight is not particularly useful. We'd like to allow developers to review top resource-consuming/long-running queries in production but without granting privileged roles like Contributor/SQL DB Contributor. They should not be able to create/manage databases, for example.

It appears as though this is possible using custom roles. Could you provide some guidance exactly which permissions are required? And maybe a hint on the page that it is possible instead of the current "all or nothing" phrasing.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: d03a2c0e-6a41-56c3-690f-48ec38b45de6
• Version Independent ID: 049a57b7-9181-e8bd-c027-5f690db1ad05
• Content: Query Performance Insight - Azure SQL Database
• Content Source: articles/sql-database/sql-database-query-performance.md
• Service: sql-database
• Sub-service: performance
• GitHub Login: @danimir
• Microsoft Alias: danil

[desmondgc]

For example, here's a custom role that can be used in combination with the built-in "Reader" role:

{
    "properties": {
        "roleName": "Query Performance Reader",
        "description": "Can view the query text of top queries.",
        "assignableScopes": [
            "/subscriptions/187b31b5-c78b-464a-93a1-e87060b29a5a"
        ],
        "permissions": [
            {
                "actions": [
                    "Microsoft.Sql/servers/databases/topQueries/queryText/action"
                ],
                "notActions": [],
                "dataActions": [],
                "notDataActions": []
            }
        ]
    }
}

[VikasPullagura-MSFT]

@desmondgc
Thanks for the Question! We are currently investigating and will get back on this.

[NavtejSaini-MSFT]

@danimir Can you please help here regarding the custom role that can be created for this?

[danimir]

Can you try to create a custom role with allowing the following actions:

Microsoft.Sql/servers/databases/read
Microsoft.Sql/servers/databases/topQueries/queryText/action
Microsoft.Sql/servers/read

Please let me know if this worked for you?

[NavtejSaini-MSFT]

@desmondgc Please let us know if you need any further help with this.

[NavtejSaini-MSFT]

@desmondgc If there are no questions at this time. We will go ahead and close this thread for now.Please get back to us if you need any further help.

[aldor007]

Hi
I have the same issue for Azure PostgreSQL DB. I've custom role

{ 
 "actions": [
       "Microsoft.Sql/servers/databases/providers/Microsoft.Insights/logDefinitions/read",
       "Microsoft.Sql/servers/databases/queryStore/read",
       "Microsoft.Sql/servers/databases/queryStore/write",
       "Microsoft.Sql/servers/databases/topQueries/read",
       "Microsoft.Sql/servers/databases/topQueries/statistics/read",
       "Microsoft.Sql/servers/databases/topQueries/queryText/action",
       "Microsoft.Sql/servers/databases/schemas/tables/columns/read",
       "Microsoft.Sql/servers/databases/schemas/read",
       "Microsoft.Sql/servers/databases/schemas/tables/read",
       "Microsoft.Sql/servers/databases/extensions/read",
       "Microsoft.Sql/servers/databases/queryStore/write",
       "Microsoft.Sql/servers/databases/*/read",
       "Microsoft.Sql/servers/databases/topQueries/statistics/read",
       "Microsoft.Insights/metrics/read",
       "Microsoft.Insights/metricDefinitions/read",
       "Microsoft.Sql/servers/automaticTuning/read",
       "Microsoft.Sql/locations/databaseOperationResults/read",
       "Microsoft.Sql/servers/databases/queryStore/queryTexts/read",
       "Microsoft.DBforPostgreSQL/servers/queryTexts/action",
       "Microsoft.DBforPostgreSQL/servers/queryTexts/read",
       "Microsoft.DBforPostgreSQL/servers/waitStatistics/read",
       "Microsoft.DBforPostgreSQL/performanceTiers/read",
       "Microsoft.DBforPostgreSQL/operations/read",
       "Microsoft.DBforPostgreSQL/servers/read",
       "Microsoft.DBforPostgreSQL/servers/configurations/read",
       "Microsoft.DBforPostgreSQL/servers/databases/read",
       "Microsoft.DBforPostgreSQL/servers/logFiles/read",
       "Microsoft.DBforPostgreSQL/servers/replicas/read",
       "Microsoft.DBforPostgreSQL/servers/topQueryStatistics/read",
       "Microsoft.DBforPostgreSQL/serversv2/providers/Microsoft.Insights/diagnosticSettings/read",
       "Microsoft.DBforPostgreSQL/serversv2/providers/Microsoft.Insights/logDefinitions/read",
       "Microsoft.DBforPostgreSQL/serversv2/providers/Microsoft.Insights/metricDefinitions/read",
       "Microsoft.DBforPostgreSQL/singleservers/providers/Microsoft.Insights/logDefinitions/read",
       "Microsoft.DBforPostgreSQL/servers/providers/Microsoft.Insights/diagnosticSettings/read",
       "Microsoft.DBforPostgreSQL/servers/migrations/read",
       "Microsoft.DBforPostgreSQL/servers/advisors/recommendedActions/read",
       "Microsoft.DBforPostgreSQL/servers/advisors/read"
    ],
"not_actions": [
  "Microsoft.Sql/managedInstances/databases/currentSensitivityLabels/*",
      "Microsoft.Sql/managedInstances/databases/schemas/tables/columns/sensitivityLabels/*",
      "Microsoft.Sql/managedInstances/databases/securityAlertPolicies/*",
      "Microsoft.Sql/managedInstances/databases/vulnerabilityAssessments/*",
      "Microsoft.Sql/managedInstances/securityAlertPolicies/*",
      "Microsoft.Sql/managedInstances/vulnerabilityAssessments/*",
      "Microsoft.Sql/servers/databases/auditingSettings/*",
      "Microsoft.Sql/servers/databases/currentSensitivityLabels/*",
      "Microsoft.Sql/servers/databases/dataMaskingPolicies/*",
      "Microsoft.Sql/servers/databases/extendedAuditingSettings/*",
      "Microsoft.Sql/servers/databases/schemas/tables/columns/sensitivityLabels/*",
      "Microsoft.Sql/servers/databases/securityAlertPolicies/*",
      "Microsoft.Sql/servers/databases/vulnerabilityAssessments/*",
      "Microsoft.Sql/servers/databases/vulnerabilityAssessmentScans/*",
      "Microsoft.Sql/servers/databases/vulnerabilityAssessmentSettings/*",
      "Microsoft.Sql/servers/vulnerabilityAssessments/*"
]
}

Which such role developer gets below error:

Query text is available only when Contributor role is assigned (which have "*" actions)

My goal - a read-only role that can see query performance text.
What action is missing in my config?