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

It certainly sounds like a unique situation. I'm basing the below in that there is no non-machine/human or business process-based solution to the issue (e.g. no policy which can be put in place to deny the client this access and so, in effect, remove the problem).

So with a technology solution, I am not aware of any purely SQL based solution to do this but my knowledge in the DBA area is limited. However, what you could do is:

1. Create an ASP.NET web application (probably Web Forms would be good here rather than MVC, but that's just my opinion) which when deployed will be set to run as an Application Pool identity as a specific service account which is created in your AD (e.g. svc_KillProcessAccount).
2. The service account would then be the thing given access to the database (taking into account whatever least privilege is required) and nobody would need to know the password except the person setting up the application pool identity (and we'll assume this is a trusted person)
3. The web application could use SMO to list and kill processes
4. I would also say the web application should write some sort of audit trail or similar of when processes were viewed and killed
5. The web application may also need security around it in terms of authentication and authorisation to limit the users who could potentially make use of it (e.g. limit it to a single AD group which is controlled by an admin somewhere)

But that's a lot of fiddling around and another application to maintain, more audit information to review etc etc. So I would consider it at least a moderately large investment which is only going to resolve (or semi-resolve) a single problem (I am assuming this situation does not come up much).

I think with SMO and the kind of power the application would have you'd need to be careful there.

So, I guess I've given a rather complicated solution. Perhaps there is an easier one that uses more to-the-bone SQL (as I said, I'm no DBA or expert on those kinds of things) but if I were proposing the kind of solution above it would really give me some pause around whether it was worthwhile or if we could find some sort of "human" or business process solution to the problem.

Sorry, that last paragraph was a bit opinionated - you probably didn't need me to be espousing all that :-)

HTH (in some way at least) and Good Luck! Nathan