2

I created a stored procedure to allow users to Close All Db connections in the QA environment. I am SA and created the procedure.

When I modify the SP , run it Without 'Execute as Owner', I get results. When I add 'Execute as Owner', I do not receive any results.

Trying to understand why.

create procedure [dbo].[DatabaseConnectionClose]
    @DatabaseName varchar(255)
with execute as owner
as


DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id(@DatabaseName)

select @kill as CloseConnectionScript

EXEC(@kill);
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306

1 Answers1

8

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:

  1. Get rid of EXECUTE AS

  2. 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

Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306