SqlDependency.Start () error: "The specified user owner cannot be found"
I am trying to create a website using Signalr and SqlDependency
. I enabled Service Broker and ran the following 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];
But when I run the website, I get the following error:
The specified user owner cannot be found.
The queue 'SqlQueryNotificationService-ce08361b-7c12-412a-a087-495959118214' cannot be found 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);
Can anyone help me find the problem?
source to share
Instead
GRANT CONTROL ON SCHEMA::[dbo] TO [SqlUser];
GRANT IMPERSONATE ON USER::DBO TO [SqlUser];
which doesn't seem like a great idea anyway, I found that you can get rid of this error by creating a user owned schema and setting that schema as the default. For example, for an existing user:
CREATE SCHEMA [SqlUser] AUTHORIZATION [SqlUser]
GO
ALTER USER [SqlUser] WITH DEFAULT_SCHEMA = [SqlUser]
Queues and stored procedures will be created within this schema in time SqlDependency.Start
.
Personally, I've had a hard time getting it to work using a schema owned by someone else, but I don't know why.
source to share