When several users need to perform similar activities in a particular database (and there is no corresponding Windows group), you can add a database role, which specifies a group of database users that can access the same objects of the database.
Members of a database role can be any of the following:
- Windows groups and user accounts
- Logins
- Other roles
The security architecture in the Database Engine includes several “system” roles that have special implicit permissions. There are two types of predefined roles (in addition to user-defined roles):
- Fixed server roles
- Fixed database roles
Beside these two, the following sections also describe the following types of roles:
- Application roles
- User-defined server roles
- User-defined database roles
The following sections describe in detail these five role types.
1. Fixed Server Roles
Fixed server roles are defined at the server level and therefore exist outside of databases belonging to the database server. Table 12-1 lists all existing fixed server roles.
2. Managing Fixed Server Roles
You can add members to and delete members from fixed server roles in two ways:
- Using SQL Server Management Studio
- Using T-SQL statements
To add a login to a fixed server role using Management Studio, expand the server, expand Security, and expand Server Roles. Right-click the role to which you want to add a login and then click Properties. On the Members page of the Server Role Properties dialog box (see Figure 12-4), click Add. Search for the login you want to add. Such login is then a member of the role and inherits all authorization properties assigned to that role.
The Transact-SQL statements CREATE SERVER ROLE and DROP SERVER ROLE are used, respectively, to add members to and delete members from a fixed server role. The ALTER SERVER ROLE statement modifies the membership of a server role. Example 12.14, later in the chapter, shows the use of the CREATE SERVER ROLE and ALTER SERVER ROLE statements.
NOTE You cannot add, remove, or rename fixed server roles. Additionally, only the members of fixed server roles can execute the system procedures to add or remove logins to or from the role.
3. Fixed Database Roles
Fixed database roles are defined at the database level and therefore exist in each database belonging to the database server. Table 12-2 lists all of the fixed database roles. Members of the fixed database role can perform different activities. Use Microsoft Docs to learn which activities are allowed for each of the fixed database roles.
Besides the fixed database roles listed in Table 12-2, there is a special fixed database role called public, which is explained next.
3.1. public Role
The public role is a special fixed database role to which every legitimate user of a database belongs. It captures all default permissions for users in a database. This provides a mechanism for giving all users without appropriate permissions a set of (usually limited) permissions. The public role maintains all default permissions for users in a database and cannot be dropped. This role cannot have users, groups, or roles assigned to it because they belong to the role by default. (Example 12.24, later in the chapter, shows the use of the public role.)
By default, the public role allows users to do the following:
- View catalog views and display information from the master system database using certain system procedures
- Execute statements that do not require permissions—for example, PRINT
3.2. Assigning a User to a Fixed Database Role
To assign a user to a fixed database role using SQL Server Management Studio, expand the server, expand Databases, expand the database, expand Security, expand Roles, and then expand Database Roles. Right-click the role to which you want to add a user and then click Properties. In the Database Role Properties dialog box, click Add and browse for the user(s) you want to add. Such an account is then a member of the role and inherits all authorization properties assigned to that role.
4. Application Roles
Application roles allow you to enforce security for a particular application. In other words, application roles allow the application itself to accept the responsibility of user authentication, instead of relying on the database system. For instance, if clerks in your company may change an employee’s data only using the existing application (and not Transact-SQL statements or any other tool), you can create an application role for the application.
Application roles differ significantly from all other role types. First, application roles have no members, because they use the application only and therefore do not need to grant permissions directly to users. Second, you need a password to activate an application role.
When an application role is activated for a session by the application, the session loses all permissions applied to the logins, user accounts and groups, or roles in all databases for the duration of the session. Because these roles are applicable only to the database in which they exist, the session can gain access to another database only by virtue of permissions granted to the guest user account in the other database. For this reason, if there is no guest user account in a database, the session cannot gain access to that database.
The next two subsections describe the management of application roles.
5. Managing Application Roles Using SQL Server Management Studio
To create an application 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 Application Role. In the Application Role dialog box, enter the name of the new role. Additionally, you must enter the password and may enter the default schema for the new role.
5.1. Managing Application Roles Using T-SQL
You can create, modify, and delete application roles using the Transact-SQL statements CREATE APPLICATION ROLE, ALTER APPLICATION ROLE, and DROP APPLICATION ROLE.
The CREATE APPLICATION ROLE statement creates an application role for the current database. This statement has two options: one to specify the password and one to define the default schema—that is, the first schema that will be searched by the server when it resolves the names of objects for this role.
Example 12.13 adds a new application role called weekly_reports to the sample database.
Example 12.13
USE sample;
CREATE APPLICATION ROLE weekly_reports
WITH PASSWORD =’x1y2z3w4!’, DEFAULT_SCHEMA =my_schema;
The ALTER APPLICATION ROLE statement changes the name, password, or default schema of an existing application role. The syntax of this statement is similar to the syntax of the CREATE APPLICATION ROLE statement. To execute the ALTER APPLICATION ROLE statement, you need the ALTER permission on the role.
The DROP APPLICATION ROLE statement removes the application role from the current database. If the application role owns any objects (securables), it cannot be dropped.
5.2. Activating Application Roles
After a connection is started, it must execute the sp_setapprole system procedure to activate the permissions that are associated with an application role. This procedure has the following syntax:
sp_setapprole [@rolename =] ‘role’ ,
[@password =] ‘password’
[,[@encrypt =] ‘encrypt_style’]
role is the name of the application role defined in the current database, password specifies the corresponding password, and encrypt_style defines the encryption style specified for the password.
When you activate an application role using sp_setapprole, you need to know the following:
- After the activation of an application role, you cannot deactivate it in the current database until the session is disconnected from the system.
- An application role is always database bound—that is, its scope is the current database. If you change the current database within a session, you are allowed to perform other activities based on the permissions in that database.
NOTE The design of application roles in SQL Server is suboptimal, because it is not uniform. To create and delete application roles, you use Transact-SQL. After that, the activation of application roles is done by a system procedure.
6. User-Defined Server Roles
You can create and remove user-defined server 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.14 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.
Example 12.14
USE master;
GO
CREATE SERVER ROLE programadmin;
ALTER SERVER ROLE programadmin ADD MEMBER mary;
7. 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 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.
7.1. Managing User-Defined Database Roles Using SSMS
To create a user-defined role using 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.
7.2. 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]
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 removes 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.15 shows how you can create and add members to a user-defined role.
Example 12.15
USE sample;
CREATE ROLE marketing AUTHORIZATION peter;
GO
ALTER ROLE marketing ADD MEMBER peter;
ALTER ROLE marketing ADD MEMBER mary;
Example 12.15 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.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.