Permission to Run SQL Server Profiler Tool on a Database

SQL Server Profiler is a great tool enabling database developers to trace SQL commands executed on a SQL Server database and focus on some specific objects by filtering options like target database, SQL objects (procedures, triggers, etc) to trace, using wildcards for object names, etc. Of course, if you are working in an enterprise environment, to execute a SQL Server Profiler trace cannot be possible everytime because of limited permissions. In this SQL Server guide, I want to show database programmers which permissions are required to run a SQL trace using SQL Profiler and how to grant those permissions using SQL commands or via SQL Server Management Studio.

Required Permissions to Execute SQL Profiler Trace

Recently I tried to execute a SQL trace using SQL Server Profiler tool on a database to see which SQL procedures are executed by a web application page and with which SQL procedure parameters. But I realized that I did not have required permissions to run SQL Server Profiler trace on that remote SQL Server instance.

The error message displayed by SQL Server Profiler showed me that I require Alter Trace permission or be a member of sysadmin fixed server role.
Here is the 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.

SQL Server Profiler required permissions to run a SQL Trace

How to Check if you have SQL Profiler Permission

SQL Server database professionals can use HAS_PERMS_BY_NAME() SQL security function to check if any specific database permission is granted to a user or role on any database or on a specific database, etc. So if you have a SQL login or a Windows user authorized on a SQL Server instance, by using Has_Perms_By_Name function, you can check if you have the required permission to run or execute a SQL trace using SQL Server Profiler tool.

In order to execute a SQL trace on SQL Server, the database user should be either have sysadmin role or Alter Trace permission.
So to test Alter Trace permissionH, here is the SQL code that you can execute to test if database user has the required authorization.


SELECT HAS_PERMS_BY_NAME(null, null, 'ALTER TRACE');

Code

As you see in following screenshot, a return of 1 means the current login user is granted Alter Trace permission. On the other hand, a 0 return means current user does not have the permission to execute SQL Profiler traces on that SQL Server instance

SQL code to check if required permission is granted

For more details and syntax of the SQL Server security function Has_Perms_By_Name(), please refer to SQL Docs

How to Grant Necessary Permissions for SQL Server Profiler Tool for SQL User

To grant a database user sysadmin role is too much for only executing SQL traces using SQL Server Profiler tool. It is more tight to grant Alter Trace permission to a specific user than assigning that user as a member of sysadmin role.

Here is the SQL command you can use to grant the target SQL users the required Alter Trace permission


-- Windows user (in [domain\username] format

GRANT ALTER TRACE TO [TR\kodyaz]

-- or SQL login

GRANT ALTER TRACE TO sql_login

Code

Of course, it is possible to grant the same permission using SQL Server Management Studio.
Launch SQL Server Management Studio and connect to the SQL Server instance.
Under Security nodes, drill down Logins and right click on the target login user and choose Properties.
Switch to Securables tab. On Explicit tab, find Alter trace permission and mark Grant checkbox. Then press OK to save your changes.

grant Alter trace permission using SQL Server Management Studio