Disconnect all users from database

This is to reply Mohit's comment on my yesterday's post "Forcefully Disconnect All Users and Drop a Database" . I thought it would be nice to have it as new post rather than just a comment.

He had mentioned one scenario where there are times when you just have to disconnect other users from the database. We can acheive it by just killing all the processes connected to that particular database. I have written small stored procedure to do this:

CREATE PROCEDURE dbo.removeConnections
@database_name SYSNAME
AS
BEGIN
DECLARE @spid INT
DECLARE @query NVARCHAR(255)

DECLARE processes CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE dbid = DB_ID(@database_name)
AND spid != @@SPID

OPEN processes

FETCH NEXT FROM processes
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'KILLing '+RTRIM(@spid)
SET @query = 'KILL '+RTRIM(@spid)
EXEC(@query)

FETCH NEXT FROM processes
INTO @spid

END
CLOSE processes
DEALLOCATE processes
END
GO

~ Kiran