| Author |
 Topic  |
|
|
Ajay Kumar
Starting Member
4 Posts |
 Posted - 02/25/2012 : 08:12:52
|
Hi,
This is Ajay Kumar. G. We had requirement that User should be able to View the processes which are running on the Server. So we've given "View Server State" to him and he was able to see all the processes on the server at any time (previously, he had only DB_Owner role for all the DBs).
But now User should be able to Kill the session which causes the issues. With "View Server State" role, he can't Kill the session, just View Only. Please let me know, which role or permission I need to provide him to Kill any Session which is running on the Server. I've tried to provide "Processadmin" and other Server roles, but he wasn't able to kill any Session.
One thing, I shouldn't give Sysadmin role to him (to Kill the Session), because he was a normal User. Does SQL Server has any specific role for Killing the sessions? Please help me... it is an emergency....
Note : Version is SQL Server 2008 R2. |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 02/25/2012 : 08:39:55
|
Requires the ALTER ANY CONNECTION permission. ALTER ANY CONNECTION is included with membership in the sysadmin or processadmin fixed server roles.
http://msdn.microsoft.com/en-us/library/ms173730.aspx
So you would do this:
use master
go
GRANT ALTER ANY CONNECTION TO TheUserName; |
 |
|
|
Ajay Kumar
Starting Member
4 Posts |
Posted - 02/25/2012 : 08:51:30
|
Hi Sunita,
Thank you for your reply. I tried to provide Alter Connection Permission also. But still, he was not able to Kill any Session. While doing "kill spid", received msg "Only user processes can be killed". But able to View all the Processes, because he has View Server State permission. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 02/25/2012 : 09:01:28
|
I did the following test:USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'testuserpassword'
GO
GRANT VIEW SERVER STATE TO TestUser
GO
GRANT ALTER ANY connection TO TestUser
GO After this, I logged in as TestUser using SSMS and was able to kill any connection. So, I am not sure what else might be the issue in your case. |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 02/25/2012 : 09:05:10
|
quote:
Originally posted by Ajay Kumar
I tried to provide Alter Connection Permission also. But still, he was not able to Kill any Session. While doing "kill spid", received msg "Only user processes can be killed".
That's because he was trying to kill a system process. As the message said, only user processes (processes that are user connections to SQL Server) may be killed.
The requirement is odd, killing sessions is not something that should be done often, only for extreme situations. So why does a user (not a DBA) want permission to kill processes, especially if he's trying to kill processes where he doesn't know what they are doing?
--
Gail Shaw
SQL Server MVP |
|
|
|
Ajay Kumar
Starting Member
4 Posts |
Posted - 02/25/2012 : 09:20:15
|
I agree with you...
I agree with you, I just used the word "Normal User"... but he is an App. Dev. who has only DB_Owner permissions on all the DBs of that particular Server. He found some users sessions causes the issue and need to Kill those ones. As per Sunita's reply, I tried from my side by giving "Alter Any Connection" permission and able to Kill the Session.
Thank you very much for your reply... |
|
|
|
GilaMonster
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 02/25/2012 : 09:37:05
|
With Alter Any Connection he'll be able to kill any user session. No one, not even a sysadmin, can kill a system process.
--
Gail Shaw
SQL Server MVP |
|
|
|
Ajay Kumar
Starting Member
4 Posts |
Posted - 02/27/2012 : 03:52:57
|
| Yeah...I got it.Thank you very much for your response.... |
|
|
| |
Topic |
|
Permissions To Kill Any Session On Server
