What Permissions does QUERYTRACEON Need?

The QUERYTRACEON hint can be particularly useful for testing the new cardinality estimator in SQL Server 2014 on individual queries using Trace Flag 2312, if your database is still using compatibility level 110:

Conversely, if you’re using the new estimator everywhere by having database compatibility set to 120, you can use the old estimator for an individual query by using QUERYTRACEON 9481:

But… Permissions?!?!

There’s a little problem. Turning on a trace flag requires high permissions. Let’s say that I’m attempting to use this hint from an account which doesn’t have superpower permissions. Here we create a login and user for app_account and grant it data reader, and execute on the dbo schema:

We can now impersonate app_account to run some tests!

Meet Error 2561, User Does Not Have Permission to Run DBCC TRACEON

When I try to run the query, things screech to a halt:

Error 2571 - no permission to run DBCC TRACEON

Oh, sad panda

Even if I try to get tricky, I can’t sneak this into a temporary procedure as app_account, either:

cannot sneak this into a temp procedure, either

Deferred name resolution doesn’t help me here. I guess it’s not a name.

Stored Procedures to the Rescue

We need a little help from a stored procedure. We’re going to revert back to our sa role, and create a procedure around our query:

Due to the magic of ownership chaining, we can now run the query as app_account:

No errors to be seen

 

This Means You Don’t have to Give Your applications Sysadmin Rights

… but you may have problems if those applications need to run a lot of adhoc queries.

Want to learn more about statistics and the new cardinality estimator? Join me and Jeremiah Peschka for our Advanced Querying and Indexing course!

 

 

Previous Post
New Free Quizzes: Indexing, Query Writing, and More
Next Post
Careful Testing the 2014 Cardinality Estimator with Trace Flags

9 Comments. Leave new

  • Great write up, Kendra.
    Another option is a plan guide. See http://spaghettidba.com/2013/02/08/using-querytraceon-in-plan-guides/

  • I was just banging my head against the wall with this last night, and ended up rolling back to compatibility level 110 instead. Now I can do some more experimenting, and hopefully resolve the issues that will enable it to go back to 120.

  • We tried this as a quick test and found that the OPTION (QUERYTRACEON XXXX) worked so we started looking closer at moving from 2012 to 2014 as we thought there was a relatively simple switch for queries to use the new CE. But now we’re trying to put it in we’ve found that this works for lower permission users in procedures but not if the statements in the procedures are in dynamic SQL. Unfortunately due to the nature of the application we have, dynamic SQL is heavily used so is there any way to get OPTION (QUERYTRACEON XXXX) to work in dynamic SQL in a procedure which is run by a user who is not sysadmin?

    • Technically you can use a plan guide. Getting SQL Server to use your plan guide can sometimes be really difficult and maddening, so approach with caution!

  • Disappointed that the stored procedure ‘trick’ doesn’t work with dynamic SQL (still get the DBCC TRACEON permission problem)… Any ideas why it doesn’t work? Or alternatively, would welcome any ideas you might have to overcoming this problems (besides don’t use dynamic sql ;-). Thanks.

    • Yeah, dynamic sql doesn’t use ownership chaining and executes in the context of the caller. I can’t immediately think of a workaround that doesn’t require them to have high permissions in some way, but I’ll keep noodling on it– wonder if there’s some creative option that I’m not thinking of.

  • The apps don’t need SA but the developers of the SPs do! Even that can be a problem. Is a problem where I am.

  • Excellent information, thank you.
    But when I create a procedure, the default is “with execute as caller”. So you would need to add a “with execute as owner” clause to the creation of the procedure. Right?

Menu
{"cart_token":"","hash":"","cart_data":""}