Member Management for Database Roles

Introduction

Now that we know how to create our own database roles it is time to look at how to manage its members.

Adding new Members to a Database Role

Let's start with adding new members to a role. For that we first need to have a role and a candidate member, so let's create them using these statements:

After executing these statements we have a new empty database role TestDatabaseRole and a new user TestUser.

To add this new user to our role we need to use the ALTER ROLE statement. The syntax is very similar to the statement to add members to server roles. Instead of ALTER SERVER ROLE we just use ALTER ROLE:

Using this statement we can add any database user as well as other user-defined database roles to our role. Adding a fixed database role however results in an error. When executing this statement you need to be in the database that the role is defined in. The new member also needs to be defined in that same database.

Selecting all current Database Role Members

To find out who the current members of a database role are we can use the sys.database_role_members catalog view. It contains two columns, the role_principal_id and the member_principal_id and returns one row for each member in each role. To include the names in the result we just need to join to sys.database_principals twice:

Executing this statement returns a result like this:

Dropping Server Role Members

To remove a current member from a role we again use the ALTER ROLE statement, this time with a DROP clause:

You can use the same select statement to confirm that the removal was successful:

Fixed Database Roles

You can use the same set of statements to manage members of fixed database roles. To add our user to e.g. the db_owner role you can use this statement:

And to select the current members of that same role just replace the role name in above select statement:

Summary

Database roles can greatly simplify security and permission management in databases. The ALTER ROLE statement allows us to add new members and remove existing ones. The sys.database_role_members catalog view can be used to retrieve a list of all current members of a database role.