Query Notification Permissions
The database user that executes a command that contains a notification subscription request must have been granted the SUBSCRIBE QUERY NOTIFICATIONS permission on the database where the command is executed. The following example shows the syntax for the GRANT statement.
use dbname
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_principal
A notification subscription is owned by the database principal that executed the command that registered the subscription. When the database drops a principal, the notification subscriptions owned by that principal are removed without producing a notification message. However, the drop operation produces a warning that contains a count of the number of query notification subscriptions dropped as a result of the DROP command.
The principal that creates the notification must have the following permissions in the database where the query runs to successfully create a notification using SqlDependency:
CREATE SERVICE
CREATE QUEUE
CREATE PROCEDURE
To receive notifications, the subscribing user must have RECEIVE permissions on the QueryNotificationErrorsQueue of the subscribing database.
GRANT RECEIVE ON QueryNotificationErrorsQueue TO login
You also need SEND permission on the destination service:
GRANT SEND ON SERVICE:://theservice to login
The user running the query must have SELECT permission on the tables being accessed for notifications.
For more information, see Administration: How-to Topics (Service Broker).