Cannot find the queue 'SqlQueryNotificationService-{guid}'

Thu, 06 Jul 2006 20:52:51 Z

I am trying to get my application running on a customer site. They are being very picky about security on the database. What are the correct permissions to setup to allow my sql user to use the Service Broker.

Currently I am receiving the following error:

Cannot fin the user 'owner', because it does not exist or you do not have permission.

Cannot find the queue 'SqlQueryNotificationService-<guid>', because it does not exist or you do not have permission.

If I make my user db_owner it seems to work on other installs.

Thanks so much,

~ Steve

Cannot find the queue 'SqlQueryNotificationService-{guid}'

Thu, 06 Jul 2006 21:00:48 Z

Based on the service name, this seems to be a SqlDependency case, isn't it?

Can you explain which command do you run when you're getting this error?

Thanks,
~ Remus

Cannot find the queue 'SqlQueryNotificationService-{guid}'

Fri, 07 Jul 2006 00:14:04 Z

I am using the code exactly from the following article in MSDN Magazine for an ASP.NET website. http://msdn.microsoft.com/msdnmag/issues/06/02/WickedCode/

Basically I'm substituting a sql database for the XML file to make things better on my end.

When I try to run the site and hit the first page, I get that error above.

I have deployed this app a 5-6 locations already, in each of those cases I have my own SQL Express database so I'm controlling the security (which I made my sql user db_owner).

This is a SQL SQL2005 Standard install which I don't have full access.

Please let me know if you need any more information.

Thanks,

~ Steve

Cannot find the queue 'SqlQueryNotificationService-{guid}'

Fri, 07 Jul 2006 01:18:55 Z

In order to use the SqlDependency infrastructure, the sql user must be able to create a procedure, a service and a queue, must be granted REFERENCES permission on the QN contract and must have 'subscribe query notifications' permission. In adition, for reasons I'm not sure I comprehend, it must have have suficient permissions over the [dbo] schema to be able to create a queue and procedure in it and be able impersonate the queue owner (that is, [dbo]).

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];

HTH,
~ Remus

Cannot find the queue 'SqlQueryNotificationService-{guid}'

Mon, 31 Jul 2006 23:20:51 Z

Of note is that you don't need to give control over the dbo schema (which seems like it would be dangerous), rather you can create a new, empty schema and give your user rights to it.

The reason for granting rights to a schema is that all of the objects the user creates (the query notification stored procedure, etc.) need to be created in some space or "schema" so you need to configure the permissions accordingly.