expunge_users

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.expunge_users') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.expunge_users
GO

CREATE PROCEDURE expunge_users
     @dbName VARCHAR(30) = ' ',
     @target VARCHAR(30) = ' ',
     @targetPassword VARCHAR(30) = ' ',
     @saPassword VARCHAR(30) = ' ',
     @path VARCHAR(50) = ' ',
     @debug VARCHAR(5) = 'false'
AS
DECLARE @bail SMALLINT,
        @CMD VARCHAR(255), 
        @CMDstatus INT,
        @myName VARCHAR(30), 
        @spidToKill VARCHAR(5)    

/* 
  Procedure expunge_users will attempt to remove all users
  from a database. Misbehaved code that leaves zombies will
  prevent the zombie spid from stopping (because its not really
  active). Long running atomic processes like a huge insert or
  update may require more time to end than a WAITING COMMAND spid.
*/

IF (@dbName <> ' ') AND (@target <> ' ') AND (@path <> ' ')
  BEGIN
    SELECT @myName = 'admin.expunge_users'
    /* If every one is not being killed, increase the @bail threshold */
    SELECT @bail = 10 
    WHILE (@bail > 0)
      BEGIN
        DELETE messenger where spid = @@SPID
        SELECT @CMD = "SELECT max(spid) FROM master..sysprocesses " +
                      "WHERE dbid = (SELECT dbid FROM master..sysdatabases " +
                      "WHERE name = '" + @dbName + "')"
        SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                      ' -dmaster -Q"' + @CMD + '" -o' + @path +
                      '\userCount.txt'
        EXEC @CMDstatus = dispatch @CMD, @myName, @debug
        IF (@CMDstatus <> 0) return @CMDstatus

        EXEC hold_semaphore 'textreader'
          TRUNCATE TABLE textreader
          SELECT @CMD = 'bcp admin..textreader in ' + @path + 
                        '\userCount.txt -c -Usa -P' + @saPassword +
                        ' -S' + @@SERVERNAME 
          EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          IF (@CMDstatus <> 0) return @CMDstatus
          INSERT messenger SELECT @@SPID, text from textreader
          TRUNCATE TABLE textreader 
          SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                        ' -dmaster -Q"master..xp_cmdshell ' + "'del " + 
                        @path + "\userCount.txt'" + '"'
          EXEC @CMDstatus = dispatch @CMD, @myName, @debug
          IF (@CMDstatus <> 0) return @CMDstatus
          INSERT messenger 
          SELECT @@SPID, RTRIM(LTRIM(text)) 
          FROM textreader
          TRUNCATE TABLE textreader     
        EXEC release_semaphore 'textreader'

        SELECT @spidToKill = (SELECT min(info) FROM messenger 
                                               WHERE spid = @@SPID
                                               AND info <> " " 	
                                               AND info NOT LIKE "---%"
                                               AND info NOT LIKE "%affected)%")
        IF (SELECT ISNUMERIC(@spidToKill))= 0
          SELECT @bail = -1
        ELSE
          BEGIN  
            IF (@target = @@SERVERNAME) 
              AND
               (CONVERT(VARCHAR(5), @@SPID) = @spidToKill)
                  BEGIN
                    SELECT @CMD = 'Cannot load database ' + @dbName + ' on ' + @target
                                   + '. This process is running in the database.'
                    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
                    RETURN @CMDStatus
                  END       
            SELECT @CMD = "kill " + @spidToKill
            SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                          ' -dmaster -Q"' + @CMD + '"'
            EXEC @CMDstatus = dispatch @CMD, @myName, @debug
            IF (@CMDstatus <> 0) return @CMDstatus
            IF @bail = 0
              BEGIN
                SELECT @CMD = 'There are active users in ' + 
                              @dbName + ' on ' + @target + 
                              ' that cannot be disconnected at this time.' 
                EXEC @CMDstatus = dispatch @CMD, @myName, @debug
                IF (@CMDstatus <> 0) return @CMDstatus
              END
            SELECT @bail = @bail - 1  
          END
      END 
    DELETE messenger where spid = @@SPID
    RETURN
  END
ELSE
  BEGIN
    PRINT "      usage: expunge_users [@dbName = <database name>, ]"
    PRINT "                           [@target = <target SQL Server>,         ]"
    PRINT "                           [@targetPassword = <target sa password>,]"
    PRINT "                           [@saPassword = <admin sa password>,     ]"
    PRINT "                           [@path = <output file location>,        ]"
    PRINT "                           [@debug = <debug  - default=false>      ]"
    RETURN 1
  END
GO