SQLServer2005：拒绝访问sp_prepexec-SQLServer2005:DenyAccesstosp_prepexec

I have a SQL Server 2005 database that is suffering from lock starvation because of some poorly behaving applications running cursors through sp_prepexec. I imagine that one of the applications running a cursor has "forgotten" to close it. I would like to deny access to sp_prepexec for specific users to prevent the lock starvation from occurring in the production database.

我有一个SQL Server 2005数据库遭受锁定饥饿,因为一些表现不佳的应用程序通过sp_prepexec运行游标。我想其中一个运行游标的应用程序已“忘记”关闭它。我想拒绝为特定用户访问sp_prepexec,以防止在生产数据库中发生锁定饥饿。

When I'm attempting the following syntax:

当我尝试以下语法时:

use master deny execute on sp_prepexec to applicationUser

在sp_prepexec上使用master deny execute到applicationUser

I have to use master because when I don't: Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

我必须使用master,因为当我不这样做时:只有当前数据库是master时,才能授予服务器作用域目录视图或系统存储过程或扩展存储过程的权限。

But the problem is that applicationUser is not a user in the master database. So I get the following error: Cannot find the user 'applicationUser', because it does not exist or you do not have permission.

但问题是applicationUser不是master数据库中的用户。所以我收到以下错误:无法找到用户'applicationUser',因为它不存在或您没有权限。

If I add that user to master it doesn't seem to prevent applicationUser from running sp_prepexec in the production database.

如果我将该用户添加到master,它似乎不会阻止applicationUser在生产数据库中运行sp_prepexec。

Can somebody point me in the right direction with this?

有人能指出我正确的方向吗?

1 个解决方案

USE master
GO
DENY EXECUTE ON production.sp_prepexec TO public
GO