Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Mar 19 '13 at 20:37

This question came from our site for system and network administrators.

3  
What risks are you trying to avoid by giving him view server state? –  mrdenny Mar 19 '13 at 20:37

1 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 creating a much larger potential problem than just granting him VIEW SERVER STATE.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.