How do I grant limited access to sysprocesses in SQL Server 2005?

Question

I'm implementing some rudimentary SQL Server monitoring to watch for excessive blocking.

I have a stored procedure that looks like this:

CREATE PROCEDURE [dbo].[bsp_GetBlockedProcessCount]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @count INT

    SELECT @count = COUNT(*)
    FROM master.dbo.sysprocesses WITH (NOLOCK)
    WHERE blocked > 0

    RETURN @count
END

When I execute this in an administrator context, I get the correct answer. When I execute this in the context of a user with limited privileges (i.e. the webserver), I get zero irrespective of the true answer.

I can think of a few solutions but I'm not happy any of them:

1. I can grant VIEW SERVER STATE privileges to the user but that's way more permission than this user should have:

   GRANT VIEW SERVER STATE TO [Webserver]
   

2. Run the proc from a SQL job and put the results somewhere for the webserver to pick up but that's messy and complicated.

3. Don't try and call this from the webserver!

I'm sure there's a better solution. Any suggestions?

Answer 1

I am glad I don't know the answers firsthand :) but I have played with EXECUTE AS and REVERT to switch between users

But here are 2 good links to start with

Switching Stored Procedure Execution Context in SQL Server using the REVERT clause

summary: you grant user to IMPERSONATE as say DBA, then revert back after

http://www.mssqltips.com/tip.asp?tip=1579

Giving Permissions through Stored Procedures

summary: i am too lazy to read to whole thing

http://www.sommarskog.se/grantperm.html