Jaa

Permissions required to run Query Store in SQL Server 2016

  • Artikkeli

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

  • Anonymous
    February 12, 2016
    Is it view server state or view database state?

    • Anonymous
      February 29, 2016
      Thanks, corrected it - it is VIEW DATABASE STATE :-)