SqlDependency not working
-
Thursday, January 17, 2013 1:03 PM
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: http://msdn.microsoft.com/en-us/library/ms188311%28v=sql.105%29.aspx and http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen:
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 (http://dimarzionist.wordpress.com/2009/04/01/how-to-make-sql-server-notifications-work/) suggests that I need to grant CONTROL and IMPERSONATE from RT to DBO. This didn't fix the issue either.)
Your suggestions on what might be wrong are greatly appreciated.Thanks.
