Member Management for Database Roles

2014-01-07 - Fundamentals, General, Security

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:

CREATE ROLE TestDatabaseRole;
CREATE USER TestUser WITHOUT LOGIN;

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:

ALTER ROLE TestDatabaseRole ADD MEMBER TestUser;

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:

SELECT  DPR.name AS role_name,
        DPM.name AS member_name
FROM    sys.database_role_members AS DRM
JOIN    sys.database_principals AS DPR
        ON DRM.role_principal_id = DPR.principal_id
JOIN    sys.database_principals AS DPM
        ON DRM.member_principal_id = DPM.principal_id
WHERE   DPR.name = 'TestDatabaseRole';

Executing this statement returns a result like this:

selecting all database role members

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:

ALTER ROLE TestDatabaseRole DROP MEMBER TestUser;

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

an empty database role

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:

ALTER ROLE db_owner ADD MEMBER TestUser;

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

SELECT  DPR.name AS role_name,
        DPM.name AS member_name
FROM    sys.database_role_members AS DRM
JOIN    sys.database_principals AS DPR
        ON DRM.role_principal_id = DPR.principal_id
JOIN    sys.database_principals AS DPM
        ON DRM.member_principal_id = DPM.principal_id
WHERE   DPR.name = 'db_owner';

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.

Categories: Fundamentals, General, Security
Tags: , , , ,