Thursday 18 October 2012

Granting users rights to run SQL profiler without SA rights

If you have a group of users (say software developers) who may occasionally need to run SQL profiler but you do not wish to grant excessive rights such as SA, you can grant the “trace” right to a security group, or indeed a user. But why would you do that?!

Personally, I create a security group called “SQL Profiler Users” and grant the trace permission to that group. If a user needs to run profiler they can simply be placed in this group.

To grant the permission, run the following query:

Use master
Go
grant Alter Trace to [YourDomain\SQL Profiler Users]