SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


login permissions to run the SQL Server Profiler


login permissions to run the SQL Server Profiler

Author
Message
Decommissioned_Account_02
Decommissioned_Account_02
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 768
Hi,

Is there a way to grant a SQL Server 2005 login permissions to run the SQL Server Profiler without granting the login the Server Role of “sysadmin”?

Thanks,
David
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18560 Visits: 14898
You need ALTER TRACE permissions at the server level. Just be aware that this IS a SERVER level permission so any user you grant this to can trace ANY database.


Use master
Go
Grant Alter Trace to Login





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Decommissioned_Account_02
Decommissioned_Account_02
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 768
Thanks Jack!
mehrdad
mehrdad
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 14
If you use domain user try following syntax :

Use master
Go
Grant Alter Trace to [Domain\Username]

note: [ ] are important!


mehr
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 1378
HI jack

other than giving sysadmin or using this script

-------------------------------------
Use master
Go
Grant Alter Trace to Login
-------------------------------------

1.if we give bulkadmin and public in server role and ddladmin,db_datareader,db_datawriter in database role also user can acess the sqlserver profiler on a particular database which they are having access

2. iam usingthis process when a application user asking to give access to run trace by sqlserver profiler
3. Is this correct or not ?
4. if it is correct is it safe

Thanks
Naga.Rohitkumar
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18560 Visits: 14898
naga.rohitkumar (10/5/2012)
HI jack

other than giving sysadmin or using this script

-------------------------------------
Use master
Go
Grant Alter Trace to Login
-------------------------------------

1.if we give bulkadmin and public in server role and ddladmin,db_datareader,db_datawriter in database role also user can acess the sqlserver profiler on a particular database which they are having access

2. iam usingthis process when a application user asking to give access to run trace by sqlserver profiler
3. Is this correct or not ?
4. if it is correct is it safe


Naga,

According to BOL, the only permissions needed to run a trace (Profiler) are ALTER TRACE at the server level. [url=http://msdn.microsoft.com/en-us/library/ms187611.aspx][/url]



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 1378
ya i too agree with u ALTER TRACE
but if we use default master the user can able to have access on all databases know

i mean it for running a profiler only to one or seleted databases

Thanks
Naga.Rohitkumar
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86426 Visits: 41098
naga.rohitkumar (10/5/2012)
ya i too agree with u ALTER TRACE
but if we use default master the user can able to have access on all databases know

i mean it for running a profiler only to one or seleted databases


Based on that, I have to ask... Why do you want to give a "user" privs to run SQL Profiler? Unless they have the ability to create a Server Side Trace (and I personally believe that no user should have that level of privs), a user could easily cripple the server by creating a client side trace.

To wit, I believe that no user should have more than PUBLIC privs on production databases and their access should be limited to only what they can return using a stored procedure that they've been given EXECUTE privs on.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18560 Visits: 14898
naga.rohitkumar (10/5/2012)
ya i too agree with u ALTER TRACE
but if we use default master the user can able to have access on all databases know

i mean it for running a profiler only to one or seleted databases


This is not possible. A user must have ALTER TRACE permissions to run Profiler/Trace and this is a server level permission NOT a database level permission. Trace is designed to be used by DBA's for auditing and troubleshooting, it is NOT designed for regular user use.

As Jeff has said, "a user could easily cripple the server by creating a client side trace."

There are options that would allow you to make trace data available to users and even to allow users to start and stop and trace designed by you (not that I'm saying that this is a great option either).

You could setup a server side trace that filters on the specific database and put that script in a stored procedure that is certificate signed and allow users to run that stored procedure that creates and starts the trace (I'd put in controls that only allow this trace to be created once). Then have another signed procedure that calls fn_trace_gettable() to query the trace files.

Another option would be to have a server-side trace that you define and have running (not necessarily the best idea either), then have a process that asynchronously copies the trace data to a table and grant rights to that table.

Neither of those options are ideal, as traces do have impact on server performance, but both are better than just granting ALTER TRACE To users.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search