Now that we know how to create our own database roles it is time to look at how to manage its members.
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.
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:
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:
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';
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.