You might want to have a look at the Sysinternals tools. They contain a tool called PSKILL.EXE.
This tool will allow you to combine things to partly automate the process of killing processes on the host.
Base Script
The following small script list all processes currently running on a SQL Server instance:
SELECT sdes.login_time,
sdes.[host_name],
sdes.host_process_id,
sdes.login_name,
sdes.nt_domain,
sdes.nt_user_name,
sdec.client_net_address,
sder.wait_type
FROM sys.dm_exec_connections AS sdec
JOIN sys.dm_exec_sessions AS sdes
ON sdes.session_id = sdec.session_id
JOIN sys.dm_exec_requests AS sder
ON sder.sql_handle = sdec.most_recent_sql_handle;
This will provide you with an overview of the running tasks.
Combining Script and PsKill.exe
Now if you were to store the PSKILL.exe from the Sysinternals tools in a directory (e.g. D:\Tools\pskill.exe) on your SQL Server, then you could create a script like the following:
SELECT '!! D:\Tools\pskill.exe \\' + sdes.[host_name] + '-u DOMAIN\Administrator -p ThePassWord ' + CAST(sdes.host_process_id AS VARCHAR(10)) + '' AS RemoteKillCommand,
sdes.login_time,
sdes.[host_name],
sdes.host_process_id,
sdes.login_name,
sdes.nt_domain,
sdes.nt_user_name,
sdec.client_net_address,
sder.wait_type
FROM sys.dm_exec_connections AS sdec
JOIN sys.dm_exec_sessions AS sdes
ON sdes.session_id = sdec.session_id
JOIN sys.dm_exec_requests AS sder
ON sder.sql_handle = sdec.most_recent_sql_handle
AND sder.wait_type = 'LCK_M_S';
This would create a result set containing the command to remotely kill the session on a remote computer.
Example Results:
+--------------------------------------------------------------------------------+-------------------------+-----------+-----------------+------------+-----------+--------------+--------------------+-----------+
| RemoteKillCommand | login_time | host_name | host_process_id | login_name | nt_domain | nt_user_name | client_net_address | wait_type |
+------------------------------------------------------------------------------------+-------------------------+-----------+-----------------+------------+-----------+--------------+--------------------+-----------+
| !! D:\Tools\pskill.exe \\Computer -u DOMAIN\Administrator -p ThePassWord 23252 | 2022-08-22 15:07:02.330 | Computer | 23252 | sa | NULL | NULL | 161.78.198.100 | LCK_M_S |
+--------------------------------------------------------------------------------+-------------------------+-----------+-----------------+------------+-----------+--------------+--------------------+-----------+
Execute RemoteKillCommand in SQLCMD Mode
You can then grab that command form the result set and execute it in a SQL Server Query Window in SQLCMD Mode (can be found under Query | SQLCMD Mode when the focus is in a query window):
!! D:\Tools\pskill \\COMPUTER -u DOMAIN\Administrator -p ThePassWord 23252
This will kill the process 23252 on the remote Computer COMPUTER using the credentials DOMAIN\Administrator and the password ThePassWord.
Of course you would require a Domain Account with sufficient permissions to remotely kill processes on a client's computer!
CAUTION
Process IDs on a client's computer can change quickly. So be sure to run the commands as quick as possible or insert a PSLIST using a similar concatenation to verify that the process is still valid on the client.
Reference Reading
Next Step
The next step would be to automatically execute the generated statement without the !! exclamation marks as a DOS Command using xp_cmdshell. I'll leave that for you to find out...
