Kill connections for a user
This can be really useful if you need to kill all the connections by a user. An example might be a out of control web application which is running under a certain user. Or because you just want to abort a single users queries because they have left an open transaction and a pile of tables locked for example.
CREATE PROCEDURE [dbo].[KillConnectionsUser] @username varchar(MAX) AS DECLARE @spid int DECLARE @sql varchar(MAX) DECLARE cur CURSOR FOR SELECT spid FROM sys.sysprocesses P JOIN sys.sysdatabases D ON (D.dbid = P.dbid) JOIN sys.sysusers U ON (P.uid = U.uid) WHERE loginame = @username AND P.spid != @@SPID OPEN cur FETCH NEXT FROM cur INTO @spid WHILE @@FETCH_STATUS = 0 BEGIN PRINT CONVERT(varchar, @spid) SET @sql = 'KILL ' + RTRIM(@spid) PRINT @sql EXEC(@sql) FETCH NEXT FROM cur INTO @spid END CLOSE cur DEALLOCATE cur GO
It is really simply to use.
Just call the stored procedure with in the following way where 'Test' happens to be the name of the user you want to kill off.
Here is a q quick example.
EXEC KillConnectionsUser @username = 'Test'
Note: If you use this on your own username it will not attempt to kill its self
in the process as I put a protection into it to prevent that issue from occurring.
Did You find this page useful?
Thanks for the feeback. Please consider sharing with others.