SQL Server 2016 – Security Permissions Needed to View Query Store Reports

query-storesql-server-2016

Query store is enabled on 2 of our production databases but I no longer have access to see the Query Store node under the database since my SA privileges were revoked (I had them temporarily for a new server).

I can't find anywhere that describes what minimum permissions are needed for access to query store reports.

Best Answer

Just use

GRANT VIEW DATABASE STATE TO [USER];

Related Solutions

SQL Server – How to View Query Store Settings for a Database

This can be found (near the end) in the sys.database_query_store_options DMV.

SELECT size_based_cleanup_mode, size_based_cleanup_mode_desc
FROM sys.database_query_store_options

SQL Server – Permissions Needed to See View Definitions via Query

Simply granting VIEW DEFINITION and SELECT permissions on INFORMATION_SCHEMA and sys schema wont give you rights to see the definition of the view.

Permissions that you added will provide you information about all objects in sys schema and information_schema.

I suppose you want to see the view definition which are created in some other schema, in which case you would have to provide the user withVIEW DEFINITION to that particular schema.

Such as : GRANT VIEW DEFINITION ON SCHEMA::SchemaWhereViewsAreCreated TO [YourUser]

Best Answer

Related Solutions

SQL Server – How to View Query Store Settings for a Database

SQL Server – Permissions Needed to See View Definitions via Query

Related Question