Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (7)   |   Related Tips: More > Profiler and Trace

Attend these FREE SQL Server 2017 webcasts >> click to register


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:

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.

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


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.

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

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;
GO
REVOKE ALTER TRACE FROM manvendra;
GO

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

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


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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

Thanks,

Sushil 

 


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

Nice article !


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

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

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

Thank you for sharing this, Mavendra


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

Hello Sir,

Nice article. Quite helpful for dev env.


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

Nice Article Manvendra.


Learn more about SQL Server tools