Disconnect all users from a database

We can disconnect all users from a database by just killing all the process connected to the database. Check below stored procedure to do this:


CREATE PROCEDURE dbo.KillAllConnection
@DB_Name SYSNAME
AS

BEGIN

DECLARE @spid INT
DECLARE @strSQL NVARCHAR(255)

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

OPEN processes
FETCH NEXT FROM processes INTO @spid

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'KILLing '+RTRIM(@spid)
SET @strSQL = 'KILL '+RTRIM(@spid)
EXEC(@strSQL)
FETCH NEXT FROM processes INTO @spid

END

CLOSE processes
DEALLOCATE processes

END

GO