Permissions required to run Query Store in SQL Server 2016


If you wondered what is the minimum permission set you need to have in order to use Query Store in SQL Server 2016, then here is the answer: you need VIEW DATABASE STATE.

I tried overcoming it by giving read permissions to all the stored procedures, DMVs and DMFs but to be honest – I did not expect it to work…and it didn’t 🙂

So, how to grant VIEW DATABASE STATE permission – well the quickest way is T-SQL:

USE MyQDSDB
GO
GRANT VIEW DATABASE STATE TO [QDS_USER];

VIEW DATABASE STATE will not provide permissions to force execution plans, flush the store, reconfigure it, etc.! For that, you will need to add the user to the db_owner fixed database role.

Note: This post is tested in CTP 3.2 of SQL Server 2016 and might not be valid when the product goes RTM.

Edit(20160213): Thanks to ErikEJ for pointing at the error in my script. 

Comments (2)

  1. ErikEJ says:

    Is it view server state or view database state?

    1. Thanks, corrected it – it is VIEW DATABASE STATE 🙂

Skip to main content