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.
Managing Application Roles Using 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.
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.8 adds a new application role called weekly_reports to the sample database.
USE sample;
CREATE APPLICATION ROLE weekly_reports
WITH PASSWORD ='x1y2z3w4!',
DEFAULT_SCHEMA =my_schema;Code language: PHP (php)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.
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']Code language: JavaScript (javascript)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.
