Wednesday, April 24, 2013

sp_kill_user

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_kill_user]    Script Date: 04/24/2013 09:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec sp_kill_user 'dw_user'
*/
ALTER proc [dbo].[sp_kill_user]
(
    @username nvarchar(1000) = 'dw_user'
)
as
begin

    SET NOCOUNT ON

    DECLARE @spid INT,
        @cnt INT,
        @sql VARCHAR(255)

    SELECT @spid = MIN(spid), @cnt = COUNT(*)
        FROM master..sysprocesses
        WHERE loginame = @username
        AND spid != @@SPID

    PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
   
    WHILE @spid IS NOT NULL
    BEGIN
        PRINT 'About to KILL '+RTRIM(@spid) 
        SET @sql = 'KILL '+RTRIM(@spid)
        EXEC(@sql) 
        SELECT @spid = MIN(spid), @cnt = COUNT(*)
            FROM master..sysprocesses
            WHERE loginame = @username
            AND spid != @@SPID 
        PRINT RTRIM(@cnt)+' processes remain.'
    END
END

No comments: