3

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?

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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.