How do I grant alter trace permission in a system that doesn't have a profiler?

Manvendra Singh Comments (9)

A user with access to the database has a requirement to run the profiler frequently to do some development work in a lower life cycle environment.The user was trying to run a program but got an error message.

A user contacted us to get appropriate access to run Profiler in a development environment so that he wouldn't have to come to DBAs every time.The error was shown in the problem section when he tried to launch the program.The user was not authorized to run Profiler so he was getting this error.

There is a graphical user interface for monitoring an instance of the Database Engine or Analysis Services.You can save the data from each event to a file or table.Only members of the server role were able to start, stop or modify traces.A new permission called ALTER TRACE was introduced due to the fact that the restriction was too inflexible for many development teams.This permission allows access to start, stop, or modify a trace, as well as being able to generate user-defined events.

Users who have ALTER TRACE permission can view queries captured in Profiler.Passwords may be contained in these queries.We recommend that you only give permission to users who are authorized to view sensitive information.

The error shows that only a member of the fixed server role can run the program.The user was not part of the server role and had permission to use ALTER TRACE.Since this user is not a DBA, we should not give him the role of sys admin.It is recommended that you don't give this permission to a user in a production environment.

The first thing we need to do is create a login id and a user and assign them access to the database.Permissions can be granted by running the below commands.

We can verify this login by running the stored procedure shown below.

In the above image, you can see that manvendra is created with the permission of the database owner.As I said before, we must not give sys admin rights to any users on database server except DBAs, so we will give this user permission to use ALTER TRACE.We can grant this access in either SSMS or T-SQL.

Let's grant the rights first.The user wants to run a profiler on the server.Pick the login for which we will give permission to run Profiler from the "Security" folder.Click on the login to get to the properties.The 4th from the top is the "Securables" tab.On the right side of the screen, click on the "Search" button.There is a window named "add objects".Click OK if you choose the third option "The server 'SERVERNAME'".

You will see your server name in the Securables section of the right window screen shot.To check the GRANT permission for ALTER TRACE, you have to select your server name.Click on the OK button if you tick the check box for GRANT permission.The login "manvendra" has access to run the profiler.

You have been granted permission to run ALTER TRACE without being a member of the fixed server role.The user manvendra has permission to run Profiler if he launches it with the same login as we assigned.

You can change this access by changing the grant permission in the same window.If the GRANT permission is checked you should click the OK button if you want to do that again.Access to run Profiler will be revoked.

By running T-SQL commands, we can assign this permission.The Master database can be used to get this privilege.Do not follow Step 3 and Step 5 if you want to assign this permission by running this T-SQL command.

The below commands can be used to remove the ALTER TRACE permission from the assigned login.

This login does not have permission to run ALTER TRACE and is not a member of the fixed server role.

I followed the steps and when I tried to log in from the created login, it showed me an error.I am unable to log in to profiler or the instance.Can you help me out?Is it a microsoft account or a windows account?I am sure I have used the correct password if it is a sql server login.

Manvendra, your acticles are very clever.You help new DBAs like me by sharing your knowledge.

I don't want to give Development user the role of database owner, can you tell me how to restrict them to their basic requirment like Select, update?

Harry is not required to be a non sa user.If a person has a grant of priviledge to ALTER TRACE, he can run profiler.

Users are not allowed to be in the role of owner.Is this required to run profiler as a non admin?