Query Performance Insight helps you to quickly identify what your longest running queries are, how they change over time, and what waits are affecting them. As a pre-requisite of using Query Performance Insight, data must exist in theQuery Store.
As per the documentation,Owner or Contributor permissions are required to view the text of the queries in Query Performance Insight. With a Reader role, a user can view charts and tables but not query text.
grant a user with minimal permissions in order to be able to view the query text, without giving full access with owner or contributor role. This is when the need of having a custom role with minimal permissions to access the query text arises.
To start a custom role from scratch, please follow the steps below.
In the Azure portal, open the SQL Server where you need the custom role to be assignable and then openAccess control (IAM).
ClickRolesand then right-click on any role in the given list and selectClone.
create custom role
3. This opens the custom roles editor. Select Start from scratchoption, then click on the JSON tab.
4. This will open your custom role in JSON format. Click on Edit button and add the following permissions to you JSON.
edit JSON 5. Add the following permissions and hit Save. You can also download your custom role as JSON file.
Note: If you get the error message "No more role assignments can be created (code: RoleAssignmentLimitExceeded)" when you try to assign a role, try to reduce the number of role assignments in the subscription. Azure supports up to 2000 role assignments per subscription. This limit includes role assignments at the subscription, resource group, and resource scopes. The2000role assignments limit per subscription is fixed and cannot be increased. Check the following document to see how you can reduce the number of role assignments.