Required Permission to Run Profiler Trace on a MS SQL Server by GRANT ALTER TRACE command
If you are not a SQL Server database administrator (DBA) or working as a t-sql / sql developer in your company you may not be the database admin of your SQLServer databases. But it is very common that sql developers frequently require to connect and run profiler traces on SQL Server installations.
I had recently had such a case with one of the SQL Server instances that I had never used before. While I'm trying to connect to the MS SQL Server instance using SQL Profiler, I get the following error message.
In order to run a trace against SQL Server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission.
This error message shows that the login name or the user name that you are using in order to connect to the SQL Server database instance, does not have the required permission to run a profiler trace on that SQL Server.
If the SQL login name or the User name is a member of sysadmin fixed server role, than the related user will have the required trace permission to execute a profiler trace on the SQL Server.
If the login is not a member of sysdamin fixed server role, a member of the sysadmin fixed role can grant the trace permission to an other login user by executing the GRANT ALTER TRACE sql statement.
USE master
GO
GRANT ALTER TRACE TO sqlProfiler
GO
A SQL Server database administrator or a member of the sysadmin fixed role can take back the granter permission or revoke the permission to run profiler trace on a SQLServer by executing the REVOKE ALTER TRACE t-sql command.
USE master
GO
REVOKE ALTER TRACE FROM sqlProfiler
GO
What is interesting for GRANT ALTER TRACE sql command is that; the login user running a profiler trace does not have active Pause or Stop buttons after a REVOKE ALTER TRACE statement is executed.
For example let's create a new sql login and GRANT ALTER TRACE to the new sql login. Start a new sql profiler trace with the new sql login on a SQL Server 2008.
Then REVOKE ALTER TRACE from the sql login. But you will see that the profiler trace is keeping on working to collect sql commands executed on the MS SQL Server 2008.
Although you can clear the trace window you have now no option to Pause or Stop the trace. Even if you are the sql developer connected to the SQLSERVER with the new created sql login, you can not even close the window using the "X" on the top right corner of the trace window.
You have to End Task SQL Server Profiler using the Windows Task Manager screen.
This causes a security issue also. Since although you have revoke the permission to run a trace on a SQL Server to a login, if the login has created a trace before you have executed the REVOKE statement, he or she will still be able to collect data from profiler until a SQL Server Database Administrator kills the related profiler process from the processes window.
So if you are a DBA (database administrator) in your company, check the currently excuting processes from the sysprocesses system view after you have executed REVOKE ALTER TRACE statement for stopping disallowed profilers.
I have given a feedback on this topic on Microsoft SQL Connect. You can find the feedback link at REVOKE ALTER TRACE command causes SQL Profiler buttons Pause and Stop Disabled.
You can also follow the response from MS SQL teams by using that link also.