Permissions required to run Query Store in SQL Server 2016

  • Article

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.