I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential internal threat).
When I run the following, it returns one row as if the user ran the sp themselves with their current permissions.
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_who] with execute as owner AS BEGIN SET NOCOUNT ON; exec master.dbo.sp_who; END
Changing the "with execute as" to "self" (I'm a sysadmin) returns the same results. I've also tried the below instead of calling sp_who, and it only returns one row.
select * from sysprocesses
It seems that the context isn't switching, or persisting, throughout the execution of the procedure. And this is to say nothing of how I'm going to allow this person to "kill" processes.
Does anyone have a solution or some suggestions to this seemly unique problem?