KILL SPID

To quickly disconnect all non admins from a database, I use this:

ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

One of the development teams recently requested that their production databases be copied to a development server on a weekly basis. In the development environment, developers have db_owner access to all of their databases.

In order to restore a database, no one can be connected to it if it already exists. Since my ALTER DATABASE statements will only work on non admins, I also need to run this to kick out developers:

DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid IN (DB_ID('Database1'), DB_ID('Database2'))

WHILE @@ROWCOUNT <> 0
BEGIN
EXEC('KILL ' + @spid)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE
dbid IN (DB_ID('Database1'), DB_ID('Database2')) AND
spid > @spid
END

Most SQL Server DBAs have a similar script to this, but I thought I'd post mine in case you don't have one already.

NOTE: I can't run the following commands as we are using SQL Litespeed. Their extended stored procedure to restore databases, master.dbo.xp_restore_database, uses multiple connections to the database.

ALTER DATABASE Database1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE Database2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE