title: OPTION(QUERYTRACEON 9481), Dynamic SQL & DBCC TRACEON error
body: <p>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.</p>
  
  <p>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).</p>
  
  <p>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;</p>
  
  <p>Short or rewriting queries or giving application service accounts SA roles, does anyone have a usable solution?</p>
  
  <p>Thank you in advance,</p>
  
  <p>Jason</p>