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
    
Who is the owner of the new schema? –  mrdenny Jan 17 '13 at 19:09
add comment

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

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

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.