Sql-server – Permissions using .NET SqlDependency

service-brokersql serversql-server-2008-r2

SQLDependency Notifications work fine logged in as Windows Domain User, using Trusted Connection. But not as SQL-User:

I created a db login, user and role as follows:

CREATE LOGIN myLogin WITH PASSWORD = 'somePW', CHECK_POLICY = OFF;
BEGIN CREATE USER myUser FOR LOGIN myLogin;
BEGIN CREATE ROLE "myRole" AUTHORIZATION [dbo];
EXECUTE sp_addrolemember N'myRole', N'myUser';

Furthermore, I added some permissions, following this link

--Database level permissions
GRANT CREATE PROCEDURE to [myRole];
GRANT CREATE QUEUE to [myRole];
GRANT CREATE SERVICE to [myRole];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [myRole];
GRANT VIEW DEFINITION TO [myRole];

--Service Broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [myRole];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [myRole];

and also tried changing the db-owner as described in this article

This DBA-Question ( aiming in the same direction ) does not provide any help.

The permissions to execute the query and do a select on the table by the SqlDependency are also set. I verified this using SSMS and execute the query with the user/pw-combination created above.

Now, I try to use the SqlDependency via .NET ( connection to DB is established with the login/user described above. Executing SqlDependency.Start( myConnectionString ) throws an exception ( translated from german ):

The specified user 'owner' was not found.
Search the queue object 'SqlQueryNotificationService-9741490d-4f04-1f4e-AD70-b198e85b2812' is not possible because the object does not exist or you do not have the required permission.
Invalid object name 'SqlQueryNotificationService-9741490d-4f04-1f4e-AD70-b198e85b2812'.

Any ideas what is missing?

Best Answer

After days of debugging, one little setting made all the difference.

If you get this error mentioned in OP, please check the owner of the DB-role used! It wasn't set to dbo in my case. Changing that made it work - see comments for history!