Re: user permissions

The question is what permissions that is needed to execute the stored


"Charles Wang[MSFT]" wrote:

Hi Ola,
Thanks for your response.
Originally I just noticed that Trapulo said he wanted to kill processes on
that DB, so I did not think of killing connections.

Anyway I would like to add more comments regarding both killing a process
and a connection. For killing a process, I just found that kill.exe is not
guaranteed to be on all the existing Windows operating systems. For a
common usage, you may write a script file and then execute it via
cscript.exe. For example:
If Wscript.Arguments.Count = 0 Then
Wscript.Echo "You must enter a PID."
End If

intPID = Wscript.Arguments.Item(0)

strComputer = "."
Set objWMIService = GetObject _
("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcessList = objWMIService.ExecQuery _
("Select * from Win32_Process Where ProcessID = " & intPID & "")
For Each objProcess in colProcessList
If you save it in drive C:, then you can run cscript.exe to execute it. In
SQL Server, you can use the following statement:
exec xp_cmdshell 'cscript c:\killproc.vbs 7640'

Regarding killing a connection, you need to ensure that your login account
has the fixed server roles: sysadmin and processadmin. I performed a test
with the following statements:
create procedure usp_killsession(@spid int)
execute as LOGIN='mylogin_with_sysadmin_processadmin_fixed_server_roles'
declare @strSQL nvarchar(200)
set @strSQL = N'KILL ' + CAST(@spid as nvarchar(20))
exec sp_executeSQL @strSQL

For your concerns of checking connections on a database, you can use the
DMVs sys.dm_exec_connections and sys.dm_exec_sessions.

You may also refer to:
KILL (Transact-SQL)

Hope this helps. Welcome your any other suggestions or concerns. Have a
nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
This posting is provided "AS IS" with no warranties, and confers no rights.