Kill all sessions for ONE user in MS SQL

So we have a bad external application that connects to the database, executes a stored procedure and gets an answer.

It never releases the database connection.

I need a script that either kills all of the sessions for one user
or
I need a script that kills all of the sessions that have been inactive for x hours for a particular database

Anyone have something like this?

There's not really a reliable way to pin sessions to a specific database (sysprocesses has this, but it relies on an active query's database context, and dm_exec_sessions has authenticating_database_id, but this relies on the connection string, login's default database, etc).

That said, this seems to meet all of your other requirements, attempting to kill all sessions using app_name, connecting as login_name, and who haven't issued a request in the last 5 hours:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'KILL ' + CONVERT(VARCHAR(11), session_id) + N';'
FROM sys.dm_exec_sessions
  WHERE [program_name] = N'app_name'
  AND login_name = N'login_name'
  AND last_request_start_time < DATEADD(HOUR, -5, SYSDATETIME());

EXEC sys.sp_executesql @sql;

If all the app does is call that one stored procedure and never does anything else at all, you could perhaps look at DBCC INPUTBUFFER for each spid, but that becomes very tedious very quickly.

StackExchange Database Administrators Q#123857, answer score: 19