SQL Server security – (Part 6)
Author: Basit A. Farooq
Editor’s note: In this part, you’ll learn how to create and manage application roles.
Application roles
You create an application role when you want an application to have a specific level of access, regardless of who’s using the application. You can use an application role to ensure that certain functions are performed only through an application.
An application role isn’t associated with a user or login. Instead, SQL Server authenticates the application using a password. The basic steps are:
- The client application connects to SQL Server using the user’s login credentials.
- The application calls sp_setapprole to connect to the database using the application role.
- SQL Server verifies the password and changes the security context to that of the application role.
One drawback to using application roles is that they are limited to database scope. When connected using an application role, the application can access other databases only as guest.
Creating an application role
You can create an application role using SQL Server Management Studio or using the CREATE APPLICATION ROLE statement. The statement has the following syntax:
CREATE APPLICATION ROLE application_role_name
WITH PASSWORD = ‘password’
[DEFAULT_SCHEMA = schema_name]
SQL Server checks password complexity for application role passwords. A complex password is one that has at least six characters and includes a mix of at least three of the following types of characters:
- Lowercase
- Uppercase
- Numeric
- Symbolic
To create an application role using SQL Server Management Studio:
- Launch SQL Server Management Studio.
- In Object Explorer, expand Databases, and then expand the database in which database role exist.
- Expand the Security folder
- Right-click Roles, then choose New, and then choose New Application Role from the menu.
- Enter the role name.
- If desired, enter the default schema.
- Enter and confirm the password.
- Select any schemas owned by the role.
- Click OK.
You assign permissions to an application role just as you would to a database user or a database role.
Modifying an application role
You can modify an application role using SQL Server Management Studio or using the ALTER APPLICATION ROLE statement. The ALTER APPLICATION ROLE statement has the following syntax:
ALTER APPLICATION ROLE application_role_name
WITH NAME = new_application_role_name
| PASSWORD = ‘password’
| DEFAULT_SCHEMA = schema_name
You can alter multiple attributes in a single call. For example, to change the password and default schema, you execute:
ALTER APPLICATION ROLE PayrollApp
WITH PASSWORD = ‘3kjfoi*ht’,
DEFAULT SCHEMA = ops
To modify an application role using SQL Server Management Studio:
- Launch SQL Server Management Studio.
- In Object Explorer, expand Databases, and then expand the database in which database role exist.
- Expand the Security folder
- Next, expand the Roles folder, and then Application Roles folder.
- Right-click the role you want to modify and choose Properties.
- Modify the properties. You can change:
- Default schema
- Password
- Owned schemas
- Securable permissions
Dropping an application role
You can drop an application role using either SQL Server Management Studio or using the DROP APPLICATION ROLE statement. The syntax for the DROP APPLICATION ROLE statement is:
DROP APPLICATION ROLE rolename
To drop an application role using SQL Server Management Studio:
- Launch SQL Server Management Studio.
- In Object Explorer, expand Databases, and then expand the database in which database role exist.
- Expand the Security folder
- Next, expand the Roles folder, and then Application Roles folder.
- Right-click the role and choose Delete.
- Click OK.
As with database roles, an application that owns securables can’t be dropped.
Continue to Part-6…