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
Last activity: Aug 23, 2018, 04:52 PM