Kill an user process with script

Kill an user process with script

• X-Tatic

  Thanks! This works just fine!
  

• Sentinel_13

  Try this

   

  declare @kill_stmt nvarchar(10), @cntr int< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  declare @kill_tbl table

        (ident int identity(1,1),

         spid int,

         loginame nvarchar(128),

         dbid int)

   

  insert into @kill_tbl(spid, loginame, dbid)

  Select sp.spid, sp.loginame, sb.dbid

  from master..sysprocesses sp

        inner join master..sysdatabases sb on sp.dbid = sb.dbid

  where sb.name = 'Test' --replace the database name with desired database name

   

  set @cntr = 1

   

  while @cntr <= (select max(ident) from @kill_tbl)

        begin

              Select @kill_stmt = 'KILL ' + convert(varchar, spid) from @kill_tbl where ident = @cntr

              exec  (@kill_stmt)

              select @cntr = @cntr + 1

        end

   

  This is kind of old but should work. If it works for you, you may want to put it in a stored procedure. A couple quick modifications and it should also work for Leonid.

  One darwback is that if users a connecting and disconnecting. The spid may change between select and the kill. There may be other got ya's but it worked for what I needed it for.

  Hope this works for you!

  
  

• Matthew!!!

  This query can be used to get the list of connections that need to be killed except your connection

  SELECT * FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID<>@@SPID

  
  

• markmangubat

  SELECT * FROM SYS.DM_EXEC_CONNECTIONS

  Server: Msg 208, Level 16, State 1, Line 1
  Invalid object name 'SYS.DM_EXEC_CONNECTIONS'.
  

  
  

• Sunny in Toronto

  Hi,

  I have the same problem, but suggested solution wouldn't resolve it.

  What I need is get 'spid' via 'sp_who' for the specific user where hostname is not on the list of machines allowed to connect using this account and then pass this/these spid(s) as a parameter(s) to KILL. This script planed to be executed as a sceduled job.

  Thanks,

  Leonid

   

  
  

• BrandonTurner

  > I am writing a script to kill any process connect to the user database
  > before daily restore job in DR machine.
  >
  > For testing, the script is getting the spid from sysprocesses and
  > sysdatabases and store the specific spid which accessing the user DB
  > into cursor and sp_who spid.
  
  You're making this way harder than it has to be:
  
  USE master
  GO
  ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  
  
  
  

  
  
• Kill an user process with script