SQlDependency的权限

Question

我有一个web服务，它使用ASP.NET缓存和SqlDependency来使缓存无效。用于SqlDependency的命令依赖于存储过程。

问题在于，web服务数据库用户至少拥有仅具有存储过程执行权限的特权。由于用户没有CREATE PROCEDURE权限，订阅时引发SqlException "CREATE PROCEDURE permission“。

如何解决为用户提供最低权限的问题？

Answer 1

SqlDependency动态创建和删除数据库对象以支持查询通知。要允许具有最低权限的用户执行这些语句，可以为SqlDependency对象创建一个单独的模式，并将其指定为只读用户的默认模式。用户当然需要对您的存储过程拥有execute权限，并且还需要模式限定的对象名(最佳实践)，它们位于不同的模式(例如dbo)中。

下面是一个带注释的示例脚本，它创建模式并将权限授予现有的最低特权用户(此处名为SqlNotificationUser )。

--create user for schema ownership
CREATE USER SqlDependencySchemaOwner WITHOUT LOGIN;
GO

--needed to allow activated cleanup proc to drop SqlDependency service owned by minimally-privileged user
GRANT ALTER ANY SERVICE TO SqlDependencySchemaOwner;
GO

--create schema for SqlDependency objects CREATE SCHEMA SqlDependency 
AUTHORIZATION SqlDependencySchemaOwner;
GO
 
--set default schema of SqlDependency schema owner (internal SqlDependecy procs do not schema-qualify object names)
ALTER USER SqlDependencySchemaOwner WITH DEFAULT_SCHEMA = SqlDependency;

--create minimally privileged login
CREATE LOGIN SqlNotificationUser WITH PASSWORD='SqlUserSt0rongP@assord';

--create minimally privileged user with default schema SqlDependency 
CREATE USER SqlNotificationUser WITH DEFAULT_SCHEMA = SqlDependency;

--grant user control permissions on SqlDependency schema
GRANT CONTROL ON SCHEMA::SqlDependency TO SqlNotificationUser;
 
--grant user impersonate permissions on SqlDependency schema owner 
GRANT IMPERSONATE ON USER::SqlDependencySchemaOwner TO SqlNotificationUser;
GO
 
--grant database permissions needed to create and use SqlDependency objects
GRANT CREATE PROCEDURE TO SqlNotificationUser;
GRANT CREATE QUEUE TO SqlNotificationUser; 
GRANT CREATE SERVICE TO SqlNotificationUser;
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO SqlNotificationUser;
GRANT VIEW DEFINITION TO SqlNotificationUser;
GRANT SELECT to SqlNotificationUser;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO SqlNotificationUser;
GRANT RECEIVE ON QueryNotificationErrorsQueue TO SqlNotificationUser;
GO