Share via

User does not have permission to run DBCC TRACEON.

  • Article

Question

Thursday, September 29, 2016 10:17 AM

am trying to add QUERYTRACEON 9481 to a dynamic query but i can not do so, as i can add the QUERYTRACEON 9481 to a store procedure however to a dynamic sql i can not add it as the user require higher rights to run the DBCC command, is there a work around to fix it, as the application depends a lot on dynamic query. This is a sample script Currently the user have read and write access to the db and execute permission

Create procedure [dbo].[test]
as
declare @sql varchar(max)
set @sql = 'SELECT TOP 1000 [ID]
      ,[name]
      ,[street_addr]
  FROM [test].[dbo].[test2] OPTION (RECOMPILE,QUERYTRACEON 9481) ' ;

EXEC (@sql)

GO

User 'test_user' does not have permission to run DBCC TRACEON.
can not depand on plan guide as am afraid it will affect the other ad-hocs

Best Regards Moug

All replies (4)

Thursday, September 29, 2016 3:54 PM âś…Answered

I would be cautious with forcing the old cardinality estimator on a larger scale. Rather, it is better to address the underlying problems with the queries. And make sure that you have trace flag 4199 enabled, so that you buy in on the corrections of the original deficiencies of the new CE. Ultimately, the old CE will go away, so using 9481 is only a temporary measure.

That said, there is a solution to the problem: sign the procedure with a certificate that you originally created in the master database. Create a login from that certificated and grant that the login the required permissions (which may be sysadmin memebership).

This article on my web site describes the technique in detail:
http://www.sommarskog.se/grantperm.html

Thursday, September 29, 2016 10:41 AM

Create your stored procedure as SA and try running again under test_user

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Thursday, September 29, 2016 1:25 PM

QUERYTRACEON can be used in plan guides. See https://spaghettidba.com/2013/02/08/using-querytraceon-in-plan-guides/ for reference.

The plan guide won't affect ad-hoc queries, as one of the parameters for the creation of the plan guide is the object id of the containing stored procedure. If not matched, it won't take the plan guide into account.

Thursday, September 29, 2016 2:23 PM | 1 vote

Hi Moug45,

  1. REVERT
  2. GO
  4. CREATE PROCEDURE dbo.RecentPosts
  5. @DateVal DATETIME
  6. AS
  7. SELECT ClosedDate, Id
  8. FROM dbo.Posts
  9. WHERE LastActivityDate > @DateVal OPTION (QUERYTRACEON 2312, RECOMPILE);
  10. GO
  12. /* Now we go back to running as app_account again */
  13. EXECUTE AS LOGIN='app_account';
  14. GO

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

Refer  more detail in following link

https://www.brentozar.com/archive/2015/06/what-permissions-does-querytraceon-need/

Please click Mark As Answer if my post helped.