Posts Tagged ‘Disconnecting users from a database’

Its frequently happens when we need to delete a database or restore a database and few users are still connected to that specific Database, it’s through an error saying that database is used by other users. Here is a script, using this we can drop/kill all connection to a specific database.

— Let Database name is VIRENDRATEST and we want to dropp all connection of this DB.

Use Master

Declare @dbname sysname
Set @dbname =

–Write the database name that you want to drop connections

Declare @spid int
Select @spid =
min(spidfrom master.dbo.sysprocesses where dbid = db_id(@dbname)
While @spid is 
not NULL

Execute (‘Kill ‘ + @spid)
Select @spid
min(spidfrom master.dbo.sysprocesses where dbid =db_id(@dbnameand spid > @spid


Print ‘All connection DROPPED!’