sp_setapprole
Activates the permissions associated with an application role in the current database.
Syntax
sp_setapprole [@rolename =] 'role' ,
[@password =] {Encrypt N 'password'} | 'password'
[,[@encrypt =] 'encrypt_style']
Arguments
[@rolename =] 'role'
Is the name of the application role defined in the current database. role is sysname, with no default. role must exist in the current database.
[@password =] {Encrypt N 'password'} | 'password'
Is the password required to activate the application role. password is sysname, with no default. password can be encrypted using the ODBC canonical Encrypt function. When using the Encrypt function, the password must be converted to a Unicode string by preceding the password with N.
[@encrypt =] 'encrypt_style'
Specifies the encryption style used by password. encrypt_style is varchar(10), and can be one of these values.
| Value | Description |
|---|---|
| None | The password is not encrypted and is passed to Microsoft® SQL Server™ as plaintext. This is the default. |
| Odbc | The password is encrypted using the ODBC canonical Encrypt function before being sent to SQL Server. This can only be specified when using either an ODBC client or the OLE DB Provider for SQL Server. DB-Library clients cannot use this option. |
Return Code Values
0 (success) and 1 (failure)
Remarks
Application roles do not have members; therefore, the user does not have to be added to the application role. When an application role is not activated, sp_setapprole has no effect on a user's membership in other roles or groups in the current database.
When an application role is activated, the permissions usually associated with the user's connection that activated the application role are ignored. The user's connection gains the permissions associated with the application role for the database in which the application role is defined. The user's connection can gain access to another database only through permissions granted to the guest user account in that database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database.
After an application role is activated with sp_setapprole, the role cannot be deactivated in the current database until the user disconnects from SQL Server.
To protect your application role password, encrypt the password using the ODBC Encrypt function and specify odbc as the encryption method.
The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction.
Permissions
Any user can execute sp_setapprole by providing the correct password for the role.
Examples
A. Activate an application role without encrypting the password
This example activates an application role named SalesAppRole, with the plaintext password AsDeFXX, created with permissions specifically designed for the application used by the current user.
EXEC sp_setapprole 'SalesApprole', 'AsDeFXX'
B. Activate an application role and encrypt the password
This example activates the Test application role with the password pswd, but encrypts the password before sending it to SQL Server.
EXEC sp_setapprole 'Test', {Encrypt N 'pswd'}, 'odbc'