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?
