Sample Header Ad - 728x90

Stored Procedure Execute as Owner Close Database Connections Not Working

2 votes
1 answer
595 views
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);
Asked by user157965
Aug 22, 2018, 05:37 PM
Last activity: Aug 23, 2018, 04:52 PM