I was recently asked what permissions were needed to force plans in query store. I’m sure I knew at one point, but at the moment I was asked, I couldn’t remember to save my life. So, I went and looked it up. In the interest of sharing, especially for the poor person who I left hanging, here’s what I found.
Permissions in Query Store
Look through the blog, you’ll find I’m pretty enamored with Query Store. I even contributed to a book on the topic (a little, it was almost all Tracy’s work on that book, I just helped out). I haven’t addressed security and Query Store.
You do need to think about security in Query Store. For example, should you give read access to Query Store to your dev team (maybe a good idea so they can easily look at performance history), you’re also possibly exposing some data. Let’s say you’re using a reporting tool that creates queries as batch commands and passes in parameters in clear text. That’s going to show up in Query Store and you may be letting developers see information they shouldn’t (maybe it’s not a perfect idea to let them see it). This is just an example.
Query Store keeps aggregate information about queries. That means, for all the stuff in your system that is prepared statements or procedures, no parameter values are kept with the queries. Now, the plans, that’s another matter. Compile time parameter values are stored with the plans (you can see them in the properties of the first operator). That might be another vector to consider.
However, our question was on plan forcing. Plan forcing is simply the ability that the Query Store provides to override (sort of) the decisions of the query optimizer. You can tell it to use a certain plan. You can use the GUI to do this. Behind the scenes, it uses sp_query_store_force_plan to get the job done. The documentation tells you the permissions there at the link. In order to force a plan, you must have ALTER permission on the database.
That is a pretty hefty permission. Huge. While I would encourage appropriate read permissions for people to use the valuable information inside Query Store, I’m not sure I’d give out this. Maybe, only a few people in the organization should be able to force plans.
Conclusion
The permissions needed for force plans are relatively hefty. To read from the Query Store you only need VIEW DATABASE STATE, a mostly benign set of permissions. While you use Query Store, do think about who access and what that means. I hope this helped answer the question.