SQL Server Home
REVOKE ALTER TRACE command causes SQL Profiler buttons Pause and Stop Disabled
as Won't Fix
4/20/2009 12:37:19 AM
User(s) can reproduce this bug
If you grant alter trace to a sql login and then revoke this permission after a trace is started with this login, the Profiler menu item STOP and PAUSE are disabled.
And you can not end the profiler trace normally. You have to end the process using the windows task manager screens.
All apply to SQL Server 2008
SQL Server 2008 - Developer Edition
Tools (SSMS, Agent, Profiler, etc.)
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
1) sqlProfiler is a public user. Not a member of sysadmin fixed role
GRANT ALTER TRACE TO sqlProfiler;
2) Start Profiler with sqlProfiler
REVOKE ALTER TRACE FROM sqlProfiler
4) Clear the trace window on Profiler with sqlProfiler user.
Try to Pause or Stop the trace. Trace window can not be closed.
Have to end the session by ending the Profiler task which might be running many traces on other systems with other logins.
Also although the Revoke is executed, trace still runs successfully. Might be considered as unsecure. Admins should also check processes after REVOKE ALTER TRACE statements.
Stop and Close trace buttons might still be enabled even the REVOKE ALTER TRACE command is executed
After REVOKE ALTER TRACE, currently running processes should be killed on the server also.
to post a comment.
Please enter a comment.
on 8/5/2010 at 5:54 PM
The problem is not reproduced on next generation of trace tool which is built on top of Extended Events and will be released in the next version of SQL Server (Right after SQL Server 2008 R2). I therefore resolve this problem as “won’t fix” for SQL Server profiler.
SQL Server team
on 5/13/2009 at 10:06 PM
It did not meet the Kilimanjaro bar - now known as SQL Server 2008 R2 (http://blogs.technet.com/dataplatforminsider/archive/2009/05/11/teched-2009-new-sql-server-innovations.aspx).
We will consider it in the next major release.
on 5/13/2009 at 5:07 AM
Thanks for your comments Shirley.
So in the next version SQL2010 aka Kilimanjaro?
on 5/12/2009 at 6:55 PM
Thank you for using Profiler and reporting the bug. We were able to repro in-house same behavior as you described.
Also although the Revoke is executed, trace still runs successfully. Might be considered as unsecure. Admins should also check processes after REVOKE ALTER TRACE statements. There are several issues you mentioned:
(1) You expected that, after REVOKE ALTER TRACE, the currently running processes started by sqlProfiler should be killed on the server also.
The current behavior is by design. The privilege, as the name indicates, only decides if the user can alter trace state. There are legitimate scenarios where DBAs might revoke the privilege from a user but choose to let that trace capture session continue to run until the stop time. Alternatively, SA or users belonging to sysadmin role can always choose to explicitly terminate those sessions.
(2) Within Profiler, Stop and Close trace buttons are grayed out, and you expect that those buttons might still be enabled. This is also be design, using the same reasoning above.
(3) You cannot end the profiler trace normally. You have to end the process using the windows task manager screens, which terminates other profiler trace sessions running under credential different from sqlProfiler.
This is an issue where Profiler cannot close that trace gracefully. However, due to the highly selective nature of the triage criteria, the fix will not make it into PCU release of SQL Server 2008. We will consider fixing this in the next release of SQL Server.
I hope the explanation above addresses your concerns. We hope to hear from you should you encounter other issues related to SQL Server tools or any other feature.
Microsoft SQL Server Program Manager
to post a workaround.
Please enter a workaround.
© 2014 Microsoft