Re: user permissions



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

/Ola



"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:
====Killproc.vbs=================
If Wscript.Arguments.Count = 0 Then
Wscript.Echo "You must enter a PID."
Wscript.Quit
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
objProcess.Terminate()
Next
==============================
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)
as
begin
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
REVERT
end

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)
http://msdn2.microsoft.com/en-us/library/ms173730.aspx

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








.



Relevant Pages

  • Re: user permissions
    ... Originally I just noticed that Trapulo said he wanted to kill processes on ... Anyway I would like to add more comments regarding both killing a process ... For your concerns of checking connections on a database, ... Please feel free to let my manager ...
    (microsoft.public.sqlserver.security)
  • Re: user permissions
    ... Originally I just noticed that Trapulo said he wanted to kill processes on ... so I did not think of killing connections. ... For killing a process, I just found that kill.exe is not ... Please feel free to let my manager ...
    (microsoft.public.sqlserver.security)
  • Re: China executes food safety cheif
    ... Everybody here claims 'mental illness' when facing the death penalty. ... That we execute some of our guilty prisoners, even after a long appeals process, undermines our credibility to protest human rights violations elsewhere. ... you'd be OK with killing someone at the moment when they're about to do something violent to a family member. ...
    (rec.food.cooking)
  • Re: Torture and execution
    ... that way formally killing a member that did not ... which means that there was no court. ... †to execute to death [= Fr. ... i. 82 Thou Aumerle, didst send two of thy men, To execute the ...
    (alt.usage.english)
  • Re: Connections query
    ... Execute method against the command. ... this bit of code opens up two connections. ... Firstly select @@connections returns +2 on my previous call to it. ... stored procedure than the first recordset. ...
    (microsoft.public.sqlserver.programming)