I'm having a really tough time finding authoritative information on how to set up and use SqlDependency caching with SQL Server 2008. We've implemented the use of sql dependency cache, and it's working in our dev environment. Unfortunately, with the default settings the user needs to have quite elevated permissions which our DBAs will not allow. So I'm trying to create a more constrained set of permissions and modify the application to use an explicitly created and permissioned broker queue. Unfortunately we're receiving the following error:
Cannot find the object "Neo_CommonQueue" because it does not exist or you do not have permissions.
this error is thrown when I call the following line of code:
SqlDependency.Start(this.ConnectionString, "Neo_CommonQueue");
Below are the permissions that I've tried to add so far to no avail.
--script for testuser
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [testuser];
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [testuser];
GO
GRANT REFERENCES ON
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [testuser];
GO
-- Create the Queue on which change notifications will be sent
CREATE QUEUE Neo_CommonQueue;
GO
-- Create the Queue on which change notifications will be sent
CREATE QUEUE Neo_CommonQueueService;
GO
-- Create the Service on the Queue
CREATE SERVICE Neo_CommonQueueService
ON QUEUE Neo_CommonQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GO
-- Grant appropriate permissions on service and queue
GRANT RECEIVE ON Neo_CommonQueueService TO [testuser];
GO
GRANT SEND ON SERVICE::[Neo_CommonQueueService] to [testuser];
GO