sp_addapprole (T-SQL)

Adds a special type of role in the current database, which is used for application security.

Syntax

sp_addapprole [@rolename =] 'role', [@password =] 'password'

Arguments

[@rolename =] 'role'

Is the name of the new role. role is sysname, with no default. role must be a valid identifier and cannot already exist in the current database.

[@password =] 'password'

Is the password required to activate the role. password is sysname, with no default. password is stored in encrypted form.

Return Code Values

0 (success) or 1 (failure)

Remarks

Microsoft® SQL Server™ roles can contain from 1 to 128 characters, including letters, symbols, and numbers. However, roles cannot:

• Contain a backslash character (\).
• Be NULL or an empty string (‘‘).

The fundamental differences between standard and application roles are:

• Application roles contain no members. Users, Microsoft Windows NT® groups, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user’s connection through a specific application(s). A user’s association with an application role results from being able to run an application that activates the role, rather than being a member of the role.
• Application roles are inactive by default. They are activated by using sp_setapprole and require a password. The password can be provided by the user, for example, through an application prompt; however, the password is usually incorporated within the application. The password can be encrypted as it is sent to SQL Server.
• When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only through permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object, regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of SQL Server.

sp_addapprole cannot be executed from within a user-defined transaction.

Permissions

Only members of the db_owner and db_securityadmin fixed database roles can execute sp_addapprole.

Examples

This example adds the new application role SalesApp to the current database with the password xyz_123.

EXEC sp_addapprole 'SalesApp', 'xyz_123'

See Also