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.