Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is Service Broker enabled on the database? –  Jon Seigel Aug 5 '13 at 15:56
    
@Jon Seigel - Sure it is. Sorry for not mention that. I added a line in the OP: "SQLDependency Notifications work fine logged in as Windows Domain User, using Trusted Connection. But not as SQL-User" –  Nico Aug 6 '13 at 7:03
    
Okay. Do you see new Service Broker objects show up in the database like it says in the linked question? Also, does it work if you run this under sa or another SQL account that's db_owner? –  Jon Seigel Aug 6 '13 at 14:01
    
No, there's no new queue in SB, executing as myUser from OP, what is strictly logical in my opinion, since SQLDependency.Start(connectionString); throws the error. If I use sa as SQL-User, everything works fine again. So i think, there is at least one permission missing. Any way to find out which? –  Nico Aug 6 '13 at 14:21
    
There's something going on with permissions alright. In the error message, the user owner -- is that copied verbatim from the error or was that anonymized? Does that user mean anything to you? From the message, it seems like the code that's running isn't actually running under the security context you think it is. –  Jon Seigel Aug 6 '13 at 16:34
show 5 more comments

1 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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.