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.

Microsoft SQL Useful Database Role to Execute Stored Procedures: db_executor

I find it convenient to create a db_executor database role to give users the ability to execute stored procedures. This way you can look at the user's roles and just know they have the ability to execute stored procedures. Otherwise you have to go look at the permissions at the database level and most people forget or don't know to look there. You create the role as follows:

-- Create a db_executor role
CREATE ROLE db_executor 

-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor 

Then add the user to the role as follows, replacing <UserAccount> with the name of the user:

EXEC sp_addrolemember N'db_executor', N'<UserAccount>'

or if you have a newer version of SQL server:

ALTER ROLE [db_executor] ADD MEMBER [<UserAccount>]

MSSQL Database/Server User/Login Mapping

This is just a quick post, mainly so that I can refer back to it when I forget the correct syntax.

In Microsoft SQL server there two separate principals. There is a principal (user) at the database level and there is a principal (login) at the server level. A login is mapped to one or more users in one or more databases. A password or an windows account is associated to the login. When you move a database or restore from one server to another, this mapping breaks and must restore it. People all too often drop the user and login and then recreated them both. If you have permissions associated to the user though, you'll have to recreate those. The best thing to do is just update the mapping (which just updates the associated SIDs in the appropriate system tables.)

USE [DatabaseName]
ALTER USER [UserAccount] WITH LOGIN = [LoginAccount]

It's simple, but since I'm not always in DB land, I find myself forgetting the syntax.