Grant permissions on all service broker objects

Question

Does anyone know how to grant a user permissions on all Service Broker objects (i.e. contract, message type, remote binding, route, or service) in a MS SQL Server database?

I know permissions can be granted on the objects one by one. But we have tens of them, so wanted to see if there is any way smart to reduce the hassle.

We don't want to use "sysadmin" as it's too much.

Any ideas would be appreciated.

Answer 1

In short, there's no built-in role like db_datareader that grants all permissions on all service broker objects. My advice would be to create a database role, grant the necessary permissions to it, and then put the appropriate users in the role. In order to bootstrap this, you can write some dynamic SQL over system views like 'sys.service_queues` to generate the grant statements for you. For example:

select concat('grant receive on ', 
   quotename(schema_name(schema_id)), 
   '.', 
   quotename(object_name(object_id)), 
   ' to [YourRole]'
)
from sys.service_queues;

Going forward, have the discipline to grant the appropriate permissions at creation time.