Sample Header Ad - 728x90

Role permissions to trigger SQL Server Service Broker

1 vote
0 answers
79 views
We have a Service Broker that listens for changes to the database and then sends messages to a queue based on the changed data. This works if the logged in user is has a server role of sysadmin or db_owner. We want this to work for other users as well but do not want them to have sysadmin or db_owner level permissions. I created a new role but I am unsure what permissions I need to grant to allow the service broker to work. Any thoughts? I created a new role and gave my test user membership in that role. I have granted the role receive on the queues and send on the services. such as:
GRANT RECEIVE ON [dbo].[QueryNotificationErrorsQueue] to [db_servicebrokeruser]
GRANT RECEIVE ON [dbo].[EventNotificationErrorsQueue] to [db_servicebrokeruser]
GRANT RECEIVE ON [dbo].[ServiceBrokerQueue] to [db_servicebrokeruser]
GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService]  to [db_servicebrokeruser]
GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/ServiceBroker/ServiceBroker]  to [db_servicebrokeruser]
GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/Notifications/EventNotificationService]  to [db_servicebrokeruser]
I still don't see the messages flowing to the queue and I don't see any errors either.
Asked by James (11 rep)
Jul 26, 2024, 08:36 PM
Last activity: Jul 27, 2024, 03:13 PM