Member Management for Server Roles

Introduction

Yesterday I wrote about how the new SQL Server 2012 user-defined server roles can help with security management. Today I would like to show you how to add and remove members from a role and how to list current members.

Adding new Members to a Server Role

Before we can add a new member to a role we first need to create a role and a login:

With those two statements executed we have an empty server role TestServerRole and a new login TestLogin.

To now add this new login as a member to the role we need to use the ALTER SERVER ROLE statement:

That adds the TestLogin as a member to the server role TestServerRole. The new member does not need to be a SQL Login. It also can be a windows login or another user-defined server role. Attempting to add a fixed server role or the login 'SA' to a user-defined server role will however result in an error.

Selecting all current Server Role Members

To confirm that adding the new member worked we can use the following T-SQL statement against the sys.server_role_members catalog view:

It lists all the current members of our server role:

Dropping Server Role Members

If we want to remove a member from a role we also have to use the ALTER SERVER ROLE statement, but this time with a DROP clause:

You can again use the above select statement to confirm that the drop was successful.

Fixed Server Roles

Member management for fixed server roles works the same way as it does with user-defined server roles. So if you want to add our new login to e.g. the sysadmin fixed server role, you can use this statement:

To list all members of the sysadmin fixed server role, just replace TestServerRole with sysadmin in above select statement like this:

Summary

The ALTER SERVER ROLE statement allows us to add new members to a server role and remove existing ones from it. The sys.server_role_members catalog view can be used to select all current members of a server role.

If you were following along, use these two statements to clean up the test principals: