SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server Tools Reviews and Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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');

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

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






Related SQL Resources

SQL Server Articles

SQL Server 2016

SQL Server 2014

SQL Server 2012

SQL Server Tools

MS SQL Server Forums









Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems