Permissions using .NET SqlDependency

Question

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?

Answer 1

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!

Answer 2

Just to elaborate on Nico's answer, this query fixes the error:

EXEC sp_changedbowner 'sa'

The error message was

TableDependency.SqlClient.Exceptions.UserWithNoPermissionException: User without permissions.