use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExpungeUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ExpungeUsers]
GO

CREATE PROCEDURE [dbo].[ExpungeUsers] 
	@database nvarchar(128),
	@maxretry int = 3            -- number of attempts to remove users from database before failing
AS
/*******************************************************************************************************
*	admin.dbo.ExpungeUsers 
*	Creator:	Bill Wunder 
*
*       Date:           3-13-2003 
*
*	Description:	get rid of connections in a database
*	       	
*	Usage:
*	     EXECUTE admin.dbo.ExpungeUsers BillWTest
*
*
*	Modifications:   
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare @spid int,
	@sqlstr nvarchar(20),
	@retry int

---------------------------------------------
-- create temp tables
---------------------------------------------
declare @spidsInDB table (spid int)

---------------------------------------------
-- set session
---------------------------------------------
SET NOCOUNT ON

set nocount on
set @maxretry = 3
set @retry = 0

---------------------------------------------
-- body of stored procedure
---------------------------------------------

-- kill spids
insert @spidsInDB
select spid
from master.dbo.sysprocesses
where spid > 50
and dbid = db_id(@database)
and spid <> @@spid

while @@rowcount > 0 and @retry <= @maxretry
	begin
		select @spid = min(spid) from @spidsInDB
		while @spid is not null
			begin
				set @sqlstr = 'kill ' + cast(@spid as varchar(10))

				exec master.dbo.sp_executesql @sqlstr

				select @spid = min(spid) from @spidsInDB
				where spid > @spid
				and spid <> @@spid
			end

		delete @spidsInDB

		set @retry = @retry + 1		

		insert @spidsInDB
		select spid
		from master.dbo.sysprocesses
		where spid > 50
		and dbid = db_id(@database)
		and spid <> @@spid
	end

-- fail if exhausted retrys
if @retry > @maxretry
	begin
		raiserror ('Unable to get users out of database. Call in the reinforcements!',10,1)
		return 1
	end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[ExpungeUsers]  TO [BOULDER\Data Center]
GO