Microsoft SQL Useful Database Role for Service Broker: db_servicebroker

If you need to use the service broker for cache expiration or what not, you should create a service broker database role called db_servicebroker. Then assign any user that require the service broker this role. Below is the code to create the db_servicebroker role. It sets up all the database permissions needed:

/** CREATE ROLE AND GIVE PERMISSIONS **/

CREATE ROLE [db_servicebroker] AUTHORIZATION [dbo]

GO

GRANT CREATE PROCEDURE TO db_servicebroker;

GRANT CREATE SERVICE TO db_servicebroker;

GRANT CREATE QUEUE TO db_servicebroker;

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO db_servicebroker;

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO db_servicebroker;

GRANT RECEIVE ON QueryNotificationErrorsQueue TO db_servicebroker;

Now any time a user requires service broker permissions you can just assign them to this role. You can also view all the users in this role and know who has service broker permissions.

I've left one thing out though, and that's schemas. The service broker uses the default schema of the user to create the needed sprocs, queues and services. Therefore there are two routes you can take. One is to use the same schema, usually dbo, and the other is to create a separate schema per user. I prefer the later. Then when you're looking at the sprocs and queues, you know who it belongs to. I also prefer separate schemas because of the about grant permissions. If you have separate schemas, the users will be sand-boxed into their own respective schema.

If you're going the single schema route, which I don't recommend, you'll need to run the following:

GRANT CONTROL ON SCHEMA::[dbo] to [db_servicebroker];

GRANT IMPERSONATE ON USER::DBO to [db_servicebroker];

Just add users to the db_servicebroker role. This basically will give whoever is in the db_servicebroker role full keys to the kingdom.  Which is not good.

A better approach is to create a separate schema per user, set the user as the schema owner and set the schema as the user's default schema, replacing <UserName> with the name of the user execute the following per user:

CREATE SCHEMA [<UserName>Schema] AUTHORIZATION [<UserName>]

ALTER USER [<UserName>] WITH DEFAULT_SCHEMA=[<UserName>Schema]

Then just add the user to the db_servicebroker role.