How to Grant Permissions to run SQL Server Profiler for a non System Admin User

By: Manvendra Singh   |   Updated: 2015-03-20   |   Comments (9)   |   Related: > Profiler and Trace

Problem

A user with SQL Server db_owner access has a requirement to run SQL Server Profiler on a frequent basis to do some development work in a lower life cycle environment. The user was trying to run SQL Server Profiler but was getting the error message below:

Here, I will describe a possible solution for this issue.

Solution

A user has contacted us for appropriate access to run SQL Server Profiler in a development environment so that he doesn't have to come to DBAs every time. He tried to launch SQL Server Profiler and received the error shown in the problem section. The user was getting this error because he was not authorized to run Profiler because of non sa user rights.

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. Only members of sysadmin server role were able to run (start, stop or modify) traces before the release of SQL Server 2005. That restriction proved to be a bit too inflexible for many development teams, and as a result, a new permission introduced in SQL Server 2005, called ALTER TRACE was introduced. This is a server-level permission, and allows access to start, stop, or modify a trace, in addition to being able to generate user-defined events.

NOTE: As per the Microsoft MSDN site "Users who have ALTER TRACE permission can view queries that are captured in Profiler. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information."

The error shown in the screenshot suggests that only a member of the sysadmin fixed server role can run SQL Server Profiler or those users who have the GRANT permission on ALTER TRACE can run SQL Server Profiler. This user was neither part of the sysadmin server role nor has GRANT permission on ALTER TRACE. Since this user is not a DBA we should not grant the sysadmin server role to anybody except DBAs on database servers. Also it is highly recommended that you not give this kind of permission to a user in production environments.

Steps to give access to run SQL Server Profiler for a non sa user

Step 1: First we should create a login id and a user and assign that user db_owner access on a database to create similar scenario. Run the below commands to grant permissions.

USE Master;
GO
CREATE LOGIN manvendra 
WITH PASSWORD = 'M@nvendr@1';
GO

USE Collection
GO
CREATE USER manvendra FOR LOGIN manvendra
GO 

exec sp_addrolemember 'db_owner', 'manvendra';
GO

Step 2: We can check this login by running the stored procedure shown below to verify it and the associated permissions.

sp_helplogins manvendra

You can see in the above screenshot that user manvendra is created with the db_owner permission on a user database. Now as I said before, we must not give sysadmin rights to any users on database servers except DBAs, so we will give this user GRANT permission on ALTER TRACE to run SQL Server Profiler. We can grant this access in either SSMS or by running T-SQL commands.

GRANT/REVOKE ALTER TRACE permission in SQL Server Management Studio

Step 3: First let's grant the rights in SSMS. Launch SQL Server Management Studio then connect to the server where the user wants to run SQL Server Profiler. Expand the "Security" folder then the "Login" folder and choose the login for which we will give permission to run Profiler. Right click on that login and select properties. Choose the "Securables" tab (which is 4th from top) in the left pane. Click on the "Search" button on right pane. Another window named "Add Objects" will appear. Choose the 3rd option "The server 'SERVERNAME'" and then click OK.

See the below screenshot for a better understanding. Focus on the items in the black rectangles.

Step 4: Now your server name will appear in the securables section in the right pane of the window as shown in the below screen shot. Select your server name and come down to the section "Permissions for SERVERNAME" to check the GRANT permission for ALTER TRACE. Now drag down to down side till the ALTER TRACE permission, tick the check box for GRANT permission and then click on the OK button. Now the login "manvendra" has access to run SQL Server Profiler.

Step 5: Now you have been granted permissions to run ALTER TRACE without being a member of the sysadmin fixed server role. This can be verified by launching SQL Server Profiler with the same login for which we assigned the permission and we can see in the below screenshot that the user manvendra has permission to run Profiler.

You can also revoke this access by unchecking the GRANT permission for ALTER TRACE in the same window. So to do that follow the same process which we did in step 3 and step 4, if the GRANT permission is checked you should uncheck the tick in the GRANT permission for ALTER trace and click the OK button. Access to run SQL Server Profiler will be revoked.

GRANT/REVOKE ALTER TRACE permission using T-SQL

Step 6: We can also assign this permission by running T-SQL commands. We can get this privilege by running the below commands in the Master database. If you want to assign this permission by running this T-SQL command then do not follow Step 3 thru Step 5 above.

-- To Grant access to a Windows Login
USE Master;
GO
GRANT ALTER TRACE TO [DomainNAME\WindowsLogin]
GO

-- To Grant access to a SQL Login
USE master;
GO
GRANT ALTER TRACE TO manvendra
GO

Now you can go ahead and launch SQL Server Profiler to verify that you have access.

Step 7:  If you want to revoke this access from the assigned login then you can run the below commands to remove the ALTER TRACE permission.

--REVOKE access FROM a SQL Login
USE Master;
GO
REVOKE ALTER TRACE FROM manvendra;
GO

-- REVOKE access FROM a Windows Login
USE master;
GO
REVOKE ALTER TRACE FROM [DomainNAME\WindowsLogin]
GO

Now this login will not be able to run SQL Server Profiler because this login is not a member of the sysadmin fixed server role nor does this login have GRANT permission to run ALTER TRACE.

Next Steps

• You can use either of the above methods to GRANT or REVOKE accesses to run SQL Server Profiler. Do not make a user a member of the sysadmin fixed server role except Database Administrators. Also, it is advisable not to give ALTER TRACE permission to a user in a production environment.
• Explore more knowledge on Microsoft SQL Server Profiler and Trace Tips and SQL Server Database Administration Tips.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips