如何在SQL Server 2005中授予对sysprocesses的有限访问权限？

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

我正在实施一些基本的SQL Server监控来监视过多的阻塞。

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:

我可以想到一些解决方案,但我对它们中的任何一个都不满意:

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

我可以向用户授予VIEW SERVER STATE权限,但这比该用户应具有的权限更多:
```
GRANT VIEW SERVER STATE TO [Webserver]
```
Run the proc from a SQL job and put the results somewhere for the webserver to pick up but that's messy and complicated.

从SQL作业运行proc并将结果放在某个地方供Web服务器使用,但这很复杂。
Don't try and call this from the webserver!

不要试图从网络服务器调用它!

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

我相信有更好的解决方案。有什么建议?

1 个解决方案

#1

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

我很高兴我不知道答案第一手:)但我玩过EXECUTE AS和REVERT来切换用户

But here are 2 good links to start with

但这里有两个很好的链接开始

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

使用REVERT子句在SQL Server中切换存储过程执行上下文

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

摘要:您将用户授予IMPERSONATE,如DBA,然后在之后还原

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

#1