-- Stored Procedure: dbo.ExpungeUsers
-- Bill WUnder

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:   12-06-2002
*
* Project:      ForwardRecovery
* Project Mgr: 
* Dev Contact: 
*
* Description: get rid of connections in a database
*         
* Usage:
      EXECUTE admin.dbo.ExpungeUsers test
    select * from admin.dbo.UnableToExpungeLog
*
*
* 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
  if @retry > 0 waitfor delay '00:00:15'
  select @spid = min(spid) from @spidsInDB
  while @spid is not null
   begin
    set @sqlstr = 'kill ' + cast(@spid as varchar(10))

    if exists(select 1 from master.dbo.sysprocesses where spid = @spid)
     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

-- most likely failed if exhausted retrys
-- log relavent info for spids still connected
if @retry > @maxretry and exists(select 1 from @spidsInDB)
 begin
  select @spid = min(spid) from @spidsInDB

  -- collect a little info on each spid that refuses to die
  while @spid is not null
   begin

    if object_id('tempdb.dbo.#inputbuffer','U') is not null
     drop table #inputbuffer
    create table #inputbuffer
     (EventType varchar(20)
     , Parameter int
     , EventInfo varchar(255))

    if object_id('dbo.UnableToExpungeLog','U') is null
     create table admin.dbo.UnableToExpungeLog
      (Id int identity(1,1) not null
      , Spid int
      , DBName varchar(128)
      , LastBatch Datetime
      , HostName varchar(128)
      , ProgramName varchar(128)
      , LoginName varchar(128)
      , Buffer varchar(255)
      , RecCreatedDt datetime 
      constraint dft_UnableToExpungeLog__RecCreatedDt default (getdate())
      , constraint pkc_UnableToExpungeLog__Id primary key clustered (id))

    insert admin.dbo.UnableToExpungeLog (Spid, DBName, LastBatch, HostName, ProgramName, LoginName)
    select @spid, db_name(dbid), last_batch, hostname, program_name, loginame
    from master.dbo.sysprocesses where spid = @spid

    insert #inputbuffer exec sp_executesql N'dbcc inputbuffer(@spid)',N'@spid int',@spid    
    update admin.dbo.UnableToExpungeLog
     set buffer = (select replace(replace(EventInfo, char(13), ' '), char(10), ' ') -- get rid of CR and LF
            from #inputbuffer)
    where id = @@identity     

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

  raiserror ('Unable to get users out of database. See table %s.admin.dbo.UnableToExpungeLog for details.',10,1,@@servername)
  return 1
 end


GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO