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.
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.
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:
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.
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:
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: