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.

The db is SQL Server 2012. All data tables are in the dbo schema. All data access is done via stored procedures in a different schema (user rt). I want to add query notifications for some tables, so I follow recommendations from these links: Link1 and Link2:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO rt;
GRANT CREATE SERVICE to rt;
GRANT CREATE QUEUE to rt;
GRANT CREATE PROCEDURE to rt;
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO rt;
GRANT RECEIVE ON [dbo].[QueryNotificationErrorsQueue] TO [rt];

The statement is select Id, Name, UpdateDate from dbo.Reference

The user RT has EXECUTE permission on the stored procedure and SELECT permissions on dbo.Reference table.

After SqlDependency.Start I can see a stored proc and a queue created in the RT schema. I can also see a new service created under ServiceBroker.

When I execute the SqlCommand with the user RT, I get an immediate notification with Info = Invalid, Source = Statement. If I execute the SqlCommand as SA, all works fine.

(Further research (Link3) suggests that I need to grant CONTROL and IMPERSONATE from RT to DBO. This didn't fix the issue either.)

Do you have any suggestions on what might be going on?

share|improve this question

migrated from stackoverflow.com Jan 17 '13 at 13:48

This question came from our site for professional and enthusiast programmers.

    
Who is the owner of the new schema? –  mrdenny Jan 17 '13 at 19:09

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.