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

By:   |   Comments (9)   |   Related: > Profiler and Trace


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:

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.

Error during launching SQL Server Profiler by a non SA account

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


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;
CREATE LOGIN manvendra 
WITH PASSWORD = 'M@nvendr@1';

USE Collection
CREATE USER manvendra FOR LOGIN manvendra

exec sp_addrolemember 'db_owner', 'manvendra';

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

sp_helplogins manvendra

Verify newly created login named 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.

Login Properties

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.

Tick the checkbox of GRANT permission

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.

Launch Profiler after assigning GRANT on ALTER TRACE

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.


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;

-- To Grant access to a SQL Login
USE master;

GRANT permission on ALTER trace by T-SQL

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;

-- REVOKE access FROM a Windows Login
USE master;

REVOKE permission to run SQL Server profiler by T-SQL

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh 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

Comments For This Article

Sunday, October 27, 2019 - 8:26:20 AM - Lakshay Arora Back To Top (82912)

Great article. Keep it up!!

However, I exactly followed the steps and when I tried to login to SQL profiler from the created login(Manvendra), it shows me error. I am unable to login to profiler nor to the instance(if I try to do so). 
Please help me out? Is it sql server login or windows login. If it is sql server login, then I am sure I have used the correct login and password.

Else if it is Windows login, then How can I use this login(Manvendra) to log into sql profile.

Thankn in Advance:):):)

Tuesday, October 23, 2018 - 4:43:03 AM - Tabish Back To Top (78032)

Your acticles are very resourceful, Manvendra. I really appreciate that you make such efforts to share your knowledge and help new DBAs like me. 


Monday, April 11, 2016 - 10:07:36 AM - Sushil k Back To Top (41190)

 Hello Manvendra,

I do not want to give Development user, the db_owner database role, can u please let me know how to costomize Application Dev user to to restrict them to their basic requirment like, Select, update




Tuesday, March 24, 2015 - 12:38:45 PM - John Thomas Back To Top (36660)

Nice article !

Sunday, March 22, 2015 - 7:09:40 AM - Manvendra Back To Top (36625)

Thank you everyone for your comments.


Harry- db_owner is not mandatory as a non sa user. Any non sa login with any level of access can run profiler if he has GRANT priviledge to ALTER TRACE.



Friday, March 20, 2015 - 7:32:08 PM - Harry Back To Top (36616)

Our company does not allow users to be in the db_owner role.  Is this required to run profiler as a non sysadmin or is there another option?

Friday, March 20, 2015 - 11:01:54 AM - Herv Back To Top (36610)

Thank you for sharing this, Mavendra

Friday, March 20, 2015 - 7:56:18 AM - Saurav Vaish Back To Top (36605)

Hello Sir,

Nice article. Quite helpful for dev env.

Friday, March 20, 2015 - 6:02:47 AM - Deepak Kumar Back To Top (36603)

Nice Article Manvendra.

get free sql tips
agree to terms