Kill SQL Connections by Login

Ever get this error message?

Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally.

You can’t do a restore will connections to that database still exist!

If you have jobs that do restores of databases for one reason or another, I bet you have seen this message. I have a job to do a nightly restore of a backup, but some users just never disconnect even though I continually shake my finger. So what do I do? I kill all the processes from problem users before starting the restore.

--Create the cursor
declare mycursor cursor
for

-- Query for processes by login and database
select spid, Loginame
from master..sysProcesses
where Loginame='someuser' and dbid=db_id('somedb')


open mycursor

declare @spid int, @loginame varchar(255), @cmd varchar(255)

-- Loop through the cursor, killing each process
Fetch NEXT FROM MYCursor INTO @spid, @loginame
While (@@FETCH_STATUS <> -1)
begin
    -- I don't really know why this is necasary, but it is.
    select @cmd = 'kill ' + cast(@spid as varchar(5))
    exec(@cmd)

    Fetch NEXT FROM MYCursor INTO @spid, @loginame
end

close mycursor
deallocate mycursor
go

I am no TSQL expert, so kudos to this article for helping me figure out why kill @spid wasn’t working.