Activates the permissions associated with an application role in the current database.
sp_setapprole [@rolename =] 'role' ,
[@password =] {Encrypt N 'password'} | 'password'
[,[@encrypt =] 'encrypt_style']
Value | Description |
---|---|
none | The password is not encrypted and is passed to Microsoft® SQL Server™ as clear text. 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. |
0 (success) and 1 (failure)
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.
Any user can execute sp_setapprole by providing the correct password for the role.
This example activates an application role named SalesAppRole, with the clear text password AsDeFXX, created with permissions specifically designed for the application being used by the current user.
EXEC sp_setapprole 'SalesApprole', 'AsDeFXX'
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'
Application Security and Application Roles | sp_dropapprole |
sp_addapprole | System Stored Procedures |