Home Dashboard Directory Help
Search

REVOKE ALTER TRACE command causes SQL Profiler buttons Pause and Stop Disabled by eralper


Status: 

Closed
 as Won't Fix Help for as Won't Fix


0
0
Sign in
to vote
Type: Bug
ID: 433676
Opened: 4/20/2009 12:37:19 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
Details
Sign in to post a comment.
Posted by Microsoft on 8/5/2010 at 5:54 PM
Hi eralper,

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.

Thanks,
Mo Lin
Program Manager
SQL Server team
Posted by Microsoft on 5/13/2009 at 10:06 PM
Hi eralper,

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.

Shirley Wang
Posted by eralper on 5/13/2009 at 5:07 AM
Thanks for your comments Shirley.
So in the next version SQL2010 aka Kilimanjaro?
Best Regards,
Posted by Microsoft on 5/12/2009 at 6:55 PM
Hi eralper,

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.

Shirley Wang
Microsoft SQL Server Program Manager
Sign in to post a workaround.