Kill all current user processes connecting to database

Sometimes you may wish to sever any user connections to a database before performing some sort of action. One way to do this, is as follows, by getting a list of all SPIDs relating to a domain user, and appending KILL to the start. Run the following SQL, then copy, paste and run the output:

select 'Kill ' + cast(spid as varchar) from sys.sysprocesses

where loginame like 'DOMAIN\%'

Alternatively, as suggested by Pinal Dave in his blog post, you could set the server to Single User Mode and back to Multi User Mode as follows. This method is very quick and neat, however, this will kill ALL connections to the database, not just those from domain users:

USE master;

GO

ALTER DATABASE AdventureWorks

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

ALTER DATABASE AdventureWorks

SET MULTI_USER;

GO

Credit for Pinal Dave blog post: http://blog.sqlauthority.com/2009/02/06/sql-server-quickest-way-to-kill-all-threads-kill-all-user-session-kill-all-processes/