Disconnect MSSQL (T-SQL) connections by user name or database

This is a quick method of identifying and terminating the SQL connections of a specific user. This can be helpful if you need to script such a task (before a back-up perhaps). Simply adjust the user and database names as required and run.

--Create a cursor
declare mycursor cursor
for

-- Identify any server process id used by your user for the database. Update fields below as required. The username or database name can be removed if you wish to disconnect all users or a user from all databases.
select spid, Loginame
from master..sysProcesses
where Loginame='username' and dbid=db_id('database-name')

open mycursor

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

-- For each row in the cursor, kill the process by spid
Fetch NEXT FROM MYCursor INTO @spid, @loginame
While (@@FETCH_STATUS <> -1)
begin
    select @cmd = 'kill ' + cast(@spid as varchar(5))
    exec(@cmd)

    Fetch NEXT FROM MYCursor INTO @spid, @loginame
end

close mycursor
deallocate mycursor
go

Be the first to comment on "Disconnect MSSQL (T-SQL) connections by user name or database"

Leave a comment

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.