Re: user permissions

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
    ... The question is what permissions that is needed to execute the stored ... 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, ...
    (microsoft.public.sqlserver.security)
  • Re: Drop all the connections to the DataBase
    ... This procedure written by Narayana Vyas Kondreddi ... limitation by killing all the active connections. ... Just run this complete script in the master database to create this ...
    (microsoft.public.sqlserver.server)
  • 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: database security
    ... example isn't killing the connections? ... Using the same login creds in ... Monitor works no probs. ...
    (comp.databases.btrieve)
  • Re: IIS and MS SQL
    ... are you releasing the recordsets and connections within the object ... just killing the object won't explicitly do this for you. ... me for Atrax's signature ...
    (microsoft.public.inetserver.iis.security)