kill_database_users

kill_database_users.sql

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[kill_database_users]    Script Date: 11/08/2012 19:24:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[kill_database_users] @arg_dbname sysname with recompile
AS

-- kills all the users in a particular database
-- dlhatheway/3M, 11-Jun-2000

declare @a_spid	smallint
declare @msg	varchar(255)
declare @a_dbid	int

select
	@a_dbid = sdb.dbid
from	master..sysdatabases sdb
where	sdb.name = @arg_dbname

declare db_users insensitive cursor for
select
	sp.spid
from	master..sysprocesses sp
where	sp.dbid = @a_dbid

open db_users

fetch next from db_users into @a_spid
while @@fetch_status = 0
	begin
	select @msg = 'kill '+convert(char(5),@a_spid)
	print @msg
	execute (@msg)
	fetch next from db_users into @a_spid
	end

close db_users
deallocate db_users