Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-01-27 : 09:39:27
|
| Hello everyone,This question is for 2005.I need to be able to allow a person to be able to use the sp_who and sp_who2 commands. I have done that by granting the login 'grant view server' permissions.But, in testing that, I found that the login can access the Activity Monitor. The problem there is that they can right-click any process and click 'Kill Process'.Can I prevent the use of Activity Monitor, yet allow the use of sp_who?Thanks, John |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-27 : 09:46:18
|
| How about giving the login a user in master and granting that execute on sp_who.Bit surprised that they can kill processes just from the permission you have given though - are you sure it's not due to something else? control server maybe?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-01-27 : 14:49:29
|
| Thanks, nigelrivett.I removed the view server permissions. Added user to master and granted exec rights on sp_who.results: The user can execute sp_who but they only see their own sessions/processes, no one else'.The goal is to enable them to see all SPIDs but to not be able to use Activity Monitor.Thanks, John |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-27 : 15:18:57
|
Nigel is right. The permissions you originally granted aren't enough to kill a process. It is possible that they see the option, but when they click it, it does nothing. Or you've granted them more than you think you have.From BOL:quote:
Requires the ALTER ANY CONNECTION permission. ALTER ANY CONNECTION is included with membership in the sysadmin or processadmin fixed server roles.
|
|
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-01-27 : 16:28:04
|
| Oh .... I made the mistake of assuming that because the login has access to Activity Monitor, it would be able to Kill a process.I tested it and the login cannot kill a process from the Activity Monitor window.Sorry for the misunderstanding. Thanks for the help. |
|
|
|
|
|
|
How to Prevent Use of Activity Monitor