Permission to Service Broker - Query Notification

To enable service broker and use it with .net application for Query Notification, the db user required following user permissions. This is the minimum permission that required by the DB User.


-- enable service broker
ALTER DATABASE [yourdatabasename] SET ENABLE_BROKER;

-- create the queue
CREATE QUEUE [yourqueuename];

CREATE SERVICE [yourservicename]
  ON QUEUE [yourqueuename]
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);



--Database level permissions
GRANT CREATE PROCEDURE to [yourusername];
GRANT CREATE QUEUE to [yourusername];
GRANT CREATE SERVICE to [yourusername];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO  [yourusername]
GRANT VIEW DEFINITION TO [yourusername];

--Service Broker permissions
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [yourusername]
GRANT RECEIVE ON [yourqueuename] TO [yourusername]
GRANT SEND ON SERVICE::[yourservicename] TO [yourusername]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [yourusername]