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?