Stored Procedure Execute as Owner Close Database Connections Not Working
Using the EXECUTE AS clause of the CREATE {module_type} statement is impersonation, but only at the Database level. The owner is "dbo" (in this case, at least), but that is just a User; a Database-level principal. The KILL command requires an Instance-level permission. By default, when using Database-level impersonation (EXECUTE AS clause or EXECUTE AS USER statement), the process is quarantined to the current Database, even if that User maps to a Login (by having the same SID) that has the appropriate permission. Because of this restriction, the process is not allowed to reach up to the Instance to check Instance-level permissions.

To do this properly:


Get rid of EXECUTE AS
Implement this using Module Signing:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level (blog post of mine; external, but has better explanation)

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? (one of my answers, here on DBA.SE)

The only permission you should need to grant to the Certificate-based Login is: ALTER ANY CONNECTION (as per the documentation for KILL)


For more info on module signing, please visit: Module Signing Info



Also, in addition to the excellent point made by Aaron Bertrand in a comment on the question, you need to be careful not to kill sessions for legitimate processes, such as SQL Server Agent, etc. You should filter on at least the program_name column of sys.dm_exec_sessions, if not also login_name (and possibly others).

However, if ALTER DATABASE [{db_name}] SET... ends up being the way to proceed, then use the following post as a guide since you won't be concerned with Instance-level permissions in that case:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level