Access rights

Question

Access rights

OnlyOneRJ

Hall of Fame

Points: 3894
More actions
September 19, 2013 at 6:33 am

#278202

How to provide only rights to execute sp_who2 & killing the blocking SPID if any without giving sysadmin rights, is this possible???
************************************
Every Dog has a Tail !!!!! :-D

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Using the KILL command requires ALTER ANY CONNECTION. http://technet.microsoft.com/en-us/library/ms173730%28v=sql.100%29.aspx

Executing a procedure requires access to the database containing the procedure, EXECUTE on the procedure and whatever permissions the procedure code itself requires.

A long-term solution is to find the source of the blocking and fix it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLJim88 SSC Eights! Points: 887 More actions · Answer 2

You can run SP_who2 with "View Server State" permissions.

grant ALTER ANY CONNECTION to USER, will allow you to kill user sessions.

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 3

SQLJim88 (9/19/2013)
You can run SP_who2 with "View Server State" permissions.
grant ALTER ANY CONNECTION to USER, will allow you to kill user sessions.

I have created a Group Named - "Dev_Team"

What will be the SQL command which will grant them to execute SP_who2 & Kill process???

************************************
Every Dog has a Tail !!!!! :-D

SQLJim88 SSC Eights! Points: 887 More actions · Answer 4

use [master]

GO

GRANT ALTER ANY CONNECTION TO [**USER**]

GO

use [master]

GO

GRANT VIEW SERVER STATE TO [**USER**]

GO

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 5

How to provide access to a user on SQL profiler??

************************************
Every Dog has a Tail !!!!! :-D

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

OnlyOneRJ (11/11/2013)
How to provide access to a user on SQL profiler??

You don't actually want a "user" to run SQL Profiler. Not even on the server side. You're just asking for a world of hurt if you do.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

Ed Wagner (9/19/2013)
Using the KILL command requires ALTER ANY CONNECTION. http://technet.microsoft.com/en-us/library/ms173730%28v=sql.100%29.aspx
Executing a procedure requires access to the database containing the procedure, EXECUTE on the procedure and whatever permissions the procedure code itself requires.
A long-term solution is to find the source of the blocking and fix it.

I also have to state that using KILL should be a last resort nearly as severe as bouncing the server because using KILL doesn't always work as expected (and it's a documented CONNECT item). It will frequently leave the connection in a permanent 0% rollback state and some of those will consume the better part of an entire CPU. The only way to fix such 0% rollbacks is to bounce the server or the service.

Consider the use of KILL as a super last resort and, for the love of the server, don't give anyone but trusted DBAs the ability to KILL any connection.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 8

I agree with Jeff. You want to be careful with KILL. I've had it work 99% of the time, but that 1% can be really, really annoying and a killer.

Instead, you ought to track down why the devs need to kill connections. Work to fix the process and remove the need to kill things.

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 9

We are only providing it on Development servers... where they want to test & check..

Its their server...... we just have to give them rights to run profiler.. but no sysadmin role

************************************
Every Dog has a Tail !!!!! :-D

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 10

If it's just dev, look at this article: http://technet.microsoft.com/en-us/library/ms187611%28v=sql.100%29.aspx

I'd script the permissions to set this up to a group, then add the devs to the group.

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 11

OnlyOneRJ (11/11/2013)
We are only providing it on Development servers... where they want to test & check..
Its their server...... we just have to give them rights to run profiler.. but no sysadmin role

Although you say its their server, you are the DBA supporting that server.

Personally, if a DEV team has a hung connection, or needed to end a connection for any reason, I would ask them to come to me to find out why.

I had a situation previously where the dev team had this access, and were frequently ending my maintenance scripts.. not ideal.