c# - SqlDependency.Start() 错误 : "Cannot find the specified user ' owner'"

Question

我正在尝试使用 Signalr 和 SqlDependency 开发一个网站。我启用了 Service Broker 并运行了以下 T SQL:

GRANT CREATE PROCEDURE TO [SqlUser];
GRANT CREATE SERVICE TO [SqlUser];
GRANT CREATE QUEUE TO [SqlUser];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SqlUser];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlUser];
GRANT CONTROL ON SCHEMA::[dbo] TO [SqlUser];
GRANT IMPERSONATE ON USER::DBO TO [SqlUser];

但是当我运行该网站时，出现以下错误:

Cannot find the specified user 'owner'.
Cannot find the queue 'SqlQueryNotificationService-ce08361b-7c12-412a-a087-495959118214', because it does not exist or you do not have permission.
Invalid object name 'SqlQueryNotificationService-ce08361b-7c12-412a-a087-495959118214'.

protected void Application_Start()  
{
    SqlDependency.Start(
        ConfigurationManager
            .ConnectionStrings["CiaosConnectionString"].ConnectionString);

    //WebApiConfig.Register(GlobalConfiguration.Configuration);

    GlobalConfiguration.Configure(WebApiConfig.Register);

谁能帮我找出问题所在？

Answer 1

代替

GRANT CONTROL ON SCHEMA::[dbo] TO [SqlUser];
GRANT IMPERSONATE ON USER::DBO TO [SqlUser];

无论如何这似乎都不是一个好主意，我发现您可以通过创建用户拥有的模式并将该模式​​设置为默认模式来消除该错误。例如，对于现有用户:

CREATE SCHEMA [SqlUser] AUTHORIZATION [SqlUser]
GO
ALTER USER [SqlUser] WITH DEFAULT_SCHEMA = [SqlUser]

队列和存储过程将在 SqlDependency.Start 期间在该模式下创建。

就我个人而言，我很难使用其他人拥有的模式让它工作，但我不知道为什么。