Custom sp_who/sp_whoUsers

Question

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?

Answer 1

First of all I question hiring someone you consider an possible internal threat, but that aside your problem is probably a login vs user problem. When you do an EXECUTE AS you are executing as a user. VIEW SERVER STATE is an instance or login level permission. You can set your database to trustworthy and then it will act as if you are using the login rather than the user. However if you are using a DBA you don't trust then you would be greating a much larger potential problem than just granting him VIEW SERVER STATE.