Cannot find the queue 'SqlQueryNotificationService-{guid}'

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.