using sp_addrolemember

To add a user or group to a role, you need to use sp_addrolemember. This procedure is easy to use as it only accepts two parameters, the role name, and the username (or group).

Roles are utilized in order to provide a layer of abstraction from permissions from being applied directly to users. While there are two different places in which roles can be defined (the server level or the database level), the sp_addrolemember procedure only works at the database level. (In order to add users or groups to the server-level roles, use: sp_addsrvrolemember).

To add a user or group to a particular role execute the following:

[cc lang=”sql”]
USE YourDatabaseHere
GO
EXEC sp_addrolemember ‘db_datareader’, ‘derekdieter’
[/cc]

This code adds my own account to the dv_datareader role for my database. While this practice may be relatively ok, it is still far better to create a custom role and assign read permissions to the custom role, then use sp_addrolemember to add all the users or the group. As an example, let’s create a sample role:

[cc lang=”sql”]
CREATE ROLE ReadExecute AUTHORIZATION dbo
[/cc]

Now that we have created the empty role, let’s assign it read & execute permissions to the entire ‘dbo’ schema. This will allow read & execute permissions for the ReadExecute role to the entire dbo schema.

[cc lang=”sql”]
use YourDatabaseHere
GO
GRANT EXECUTE ON SCHEMA::[dbo] TO [ReadExecute]
GO
use YourDatabaseHere
GO
GRANT SELECT ON SCHEMA::[dbo] TO [ReadExecute]
GO
[/cc]

Finally, we add the role members. Remember, you can either assign one or more groups to a role, or one or more users to a role. Either way utilizes the same syntax.

[cc lang=”sql”]
USE YourDatabaseHere
GO
EXEC sp_addrolemember ‘ReadExecute’, ‘derekdieter’
GO
EXEC sp_addrolemember ‘ReadExecute’, ‘DeveloperGroup’
[/cc]