Wanting your non-sysadmin users to enable certain trace flags without changing your app?

Even with SQL Server support for so many years, we still face something new almost every day.   Sometimes you will just have to combine things together to achieve what you need.  Here is an example due to troubleshooting a customer’s issue.

A couple of months ago, we ran into a need to enable a trace flag when troubleshooting a highly critical performance issue.  This customer had 30 databases that served many applications on a single server.   One application produced queries that negatively impacted entire server. Through troubleshooting, we discovered a trace flag (which is rarely used by the way) helped query plans for that set of queries.   The problem is that the trace flag is not suited for entire server because it would negatively impact other queries.

The initial thought is to enable the trace flag at session level.  We ran into two challenges.  First, application needs code change (which they couldn’t do) to enable it.  Secondly, dbcc traceon requires sysadmin rights.   Customer’s application used a non-sysadmin user.  These two restrictions made it seem impossible to use the trace flag.

However, we eventually came up with a way of using logon trigger coupled with wrapping the dbcc traceon command inside a stored procedure.   In doing so, we solved all problems.  We were able to isolate the trace flag just to that application without requiring sysadmin login.

Below is the code of using trace flag 9481.  I used trace flag 9481 in the demo here because it’s easier to verify the fact it indeed takes effect.

 

alter database master set trustworthy on
go

use master

go
create procedure proc_enable_tf
with   execute as owner
as
Exec('dbcc traceon(9481)')

go
grant execute on proc_enable_tf to public
go

create  TRIGGER trigger_enable_tf
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF app_name()= 'Microsoft SQL Server Management Studio - Query'    -- replace this with your application name
begin
exec master.dbo.proc_enable_tf
end
END;

 

After you execute above code on SQL Server 2014, you can create a login that is not member of sysadmin.  Then log in with that user using Management studio and run some query to gather xml query plan.  In the query plan, you can examine CardinalityEstimationModelVersion to see it’s 70 (instead of 120 which is default).

you can also see in message “DBCC TRACEON 9481, server process ID (SPID) 58. This is an informational message only; no user action is required” in the errorlog.

QUERYTRACEON

QUERYTRACEON documented in https://support.microsoft.com/en-us/kb/2801413  also requires sysadmin rights.   But you can use the same approach in this blog to enable querytraceon inside a stored a procedure and grant execute permission to a less privileged user.

 

Reference:

Optimizer trace flags are documented in  https://support.microsoft.com/en-us/kb/2801413 and https://support.microsoft.com/en-us/kb/920093.

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

• Anonymous
  December 29, 2015
  The comment has been removed