August 8, 2019 at 3:55 pm
I'm in a bit of an odd-ball situation... a) We are in the process of upgrading all SQL Server instances to 2017. b) More than a few of the impacted databases are using legacy Compatability Levels/Cardinality Estimator. c) We would like to have all databases on the current (2017) CL and using the most recent CE.
The plan is to use "OPTION(QUERYTRACEON 9481)" at the individual statement level to deal with queries that don't play nice with the new CE. Not as a permanent fix, but as a means to get over the hump (rewriting every impacted procedure is simply outside the scope of the current project).
Cutting to the chase... This appears to be working as expected EXCEPT when the stored procedure is using dynamic SQL and is being executed by a non-SA user. This does, of course, make sense. As long as the "OPTION(QUERYTRACEON 9481)" is executed in the scope of the procedure, ownership chaining provides the permissions necessary to execute the underlying DBCC TRACEON command. The use of dynamic SQL, however, essentially treats the code as AD-HOC SQL and is executed under the users own security context... Which is not a SA and therefore does not have rights to execute DBCC TRACEON;
Short or rewriting queries or giving application service accounts SA roles, does anyone have a usable solution?
Thank you in advance,
Jason
August 8, 2019 at 9:27 pm
Using "OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'))" instead of "OPTION(QUERYTRACEON 9481)" looks like it may be the answer. I need to do more testing before closing the question but I did want to post an update.
June 22, 2024 at 4:16 am
Jason, were you able to succeed?
We too do have SP using dynamic SQL. We are on move towards 2022 from 2014.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply