Disconnect Users from Database

Recently I came across a situation where I wanted to take a database offline, but someone at work was connected to this database and had left the work. I could have rebooted his machine to disconnect the user but I didn't want to do that. So, I used the power of TSQL to disconnect this user and any others users connected to this database.

USE MASTER
GO

DECLARE @DBName varchar(50), @spid varchar(10)

SET @DBName= 'Orders'

WHILE(EXISTS(SELECT * FROM sysprocesses WHERE dbid= db_id('Orders')))
    BEGIN
        DECLARE spids CURSOR FAST_FORWARD FOR
               SELECT convert(varchar, spid) FROM sysprocesses
                       WHERE dbid = db_id(@DBName)
        OPEN spids
        FETCH NEXT FROM spids INTO @spid
        WHILE @@FETCH_STATUS = 0
        BEGIN
               EXEC('kill ' + @spid)
               FETCH NEXT FROM spids INTO @spid
        END
        CLOSE spids
        DEALLOCATE spids
   END
IF NOT exists(Select * FROM sysprocesses WHERE dbid = db_id(@DBName))
        EXEC sp_dboption @DBName, offline, true
ELSE
    BEGIN
        PRINT 'The following processes are still using the database:'
        SELECT spid, cmd, status, last_batch, open_tran, program_name, hostname
        FROM sysprocesses
        WHERE dbid = db_id(@DBName)
     END

GO

This is one of many useful queries in my arsenal that I use from time to time to get the job done. You may find it useful.

Thank you.