There can be times that you need to kill all connections to a data so that you can perform emergency maintenance. Or for some suitable reason either way you want to disappear the current people who are logged in.

So here is a little stored procedure that will do just that.

CREATE PROCEDURE [dbo].[KillConnectionsdb] @dbname varchar(MAX)
AS
	DECLARE @spid int
	DECLARE @sql varchar(MAX)

	DECLARE cur CURSOR FOR
		SELECT spid FROM sys.sysprocesses P
			JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
			WHERE LTRIM(RTRIM(D.Name)) = @dbname
			AND P.spid != @@SPID

	OPEN cur

	FETCH NEXT FROM cur
		INTO @spid
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT CONVERT(varchar, @spid)

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

		FETCH NEXT FROM cur
			INTO @spid
	END

	CLOSE cur
	DEALLOCATE cur

GO

It is really simply to use.

Just call the stored procedure with in the following way where 'Testing' happens to be the name of the database you want to kill off all the connections.

EXEC KillConnectionsdb @dbname = 'Testing'

Note: the stored procedure will avoid attempting to kill its own connection in the process.