xOustUsers

				
--**************************************
-- Name: xOustUsers
-- Description:OustUser is a Stored Procedure that kills all of the processes for a specified database. I mainly find this useful when I have to restore a database; someone always leaves some connection open and causes the restore to fail.
-- By: Bruce Donohue
--
-- Inputs:The database name to oust users from
--
-- Returns:Returns a single record with the value 'Ousted' if successful.
Raises custom error messages for errors encountered.
--
-- Assumes:You must be SA to create and run this.
--
-- Side Effects:Cannot oust itself. If you want to oust everyone in Northwind, you cannot run the SP from the Northwind database.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=606&lngWId=5--for details.--**************************************

use master
go
if exists (select id from sysobjects where id = object_id('dbo.xOustUsers') )
	drop procedure dbo.xOustUsers
go
create procedure dbo.xOustUsers
	@sDB	varchar(30)
as
set nocount on
begin
declare @sVal	varchar(60),
	@nCount	int
	
if exists(select dbid from master..sysdatabases where name = @sDB )
	begin
	if @sDB <> db_name()
		begin
	
		Declare cKill Cursor for 
			select 'kill ' + convert(varchar,spid)
			from 	master..sysprocesses sp,
				master..sysdatabases sd
			where	sp.dbid = sd.dbid and
				sd.name = @sDB
		Open cKill
		 
		fetch next from cKill into @sVal
		 
		While @@FETCH_STATUS = 0
			Begin
			
			exec(@sVal)
			
			fetch next from cKill into @sVal
			End
		 
		Close cKill
		Deallocate cKill
		select @nCount = count(sd.dbid)
		from 	master..sysprocesses sp,
			master..sysdatabases sd
		where	sp.dbid = sd.dbid and
			sd.name = @sDB
		if @nCount = 0 
			begin
			select 'Ousted' Successful
			end
		else
			begin
			select 'Cannot Kill SPID:' + convert(varchar,sp.spid) + ' ' + program_name Error
			from 	master..sysprocesses sp,
				master..sysdatabases sd
			where	sp.dbid = sd.dbid and
				sd.name = @sDB
			end
		end
	else
		begin
		raiserror 50001 'You cannot run this in the database you wish to oust everyone from'
		end
	end
else
	begin
	raiserror 50002 'Invalid Database Name'
	end
end
GO
 
--exec master..xOustUsers 'NorthWind'