User-Defined Server Roles
SQL Server 2012 introduces user-defined server roles. You can create and delete such roles using T-SQL statements CREATE SERVER ROLE and DROP SERVER ROLE, respectively. To add or delete members from a role, use the ALTER SERVER ROLE statement. Example 12.9 shows the use of the CREATE SERVER ROLE and ALTER SERVER ROLE statements. It creates a user-defined server role called programadmin and adds a new member to it.
USE master;
GO
CREATE SERVER ROLE programadmin;
ALTER SERVER ROLE programadmin ADD MEMBER mary;Code language: PHP (php)User-Defined Database Roles
Generally, user-defined database roles are applied when a group of database users needs to perform a common set of activities within a database and no applicable Windows group exists. These roles are created and deleted using either SQL Server Management Studio or the Transact-SQL statements CREATE ROLE, ALTER ROLE, and DROP ROLE.
The following two subsections describe the management of user-defined database roles.
Managing User-Defined Database Roles Using Management Studio
To create a user-defined role using SQL Server Management Studio, expand the server, expand Databases, and then expand the database and its Security folder. Right-click Roles, click New, and then click New Database Role. In the Database Role dialog box (see Figure 12-5), enter the name of the new role. Click Add to add members to the new role. Choose the members (users and/or other roles) of the new role and click OK.
Managing User-Defined Database Roles Using T-SQL
The CREATE ROLE statement creates a new user-defined database role in the current database. The syntax of this statement is
CREATE ROLE role_name [AUTHORIZATION owner_name]Code language: CSS (css)role_name is the name of the user-defined role to be created. owner_name specifies the database user or role that will own the new role. (If no user is specified, the role will be owned by the user that executes the CREATE ROLE statement.)
The ALTER ROLE statement changes the name of a user-defined database role. Similarly, the DROP ROLE statement drops a role from the database. Roles that own database objects (securables) cannot be dropped from the database. To drop such a role, you must first transfer the ownership of those objects.
Example 12.10 shows how you can create and add members to a user-defined role.
USE sample;
CREATE ROLE marketing AUTHORIZATION peter;
GO
ALTER ROLE marketing ADD MEMBER 'peter';
ALTER ROLE marketing ADD MEMBER 'mary';Code language: PHP (php)Example 12.10 first creates the user-defined role called marketing, and then, using the ADD MEMBER clause of the ALTER ROLE statement, adds two members, peter and mary, to the role.
