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.