sp_setapprole (Transact-SQL)

Activates the permissions associated with an application role in the current database.

Transact-SQL Syntax Conventions

Syntax

sp_setapprole [ @rolename = ] 'role',
    [ @password = ] { encrypt N'password' } 
      |
        'password' [ , [ @encrypt = ] { 'none' | 'odbc' } ]
        [ , [ @fCreateCookie = ] true | false ]
    [ , [ @cookie = ] @cookie OUTPUT ]

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' }
  Is the password required to activate the application role. password is sysname, with no default. password can be obfuscated by using the ODBC encrypt function. When you use the encrypt function, the password must be converted to a Unicode string by placing N before the first quotation mark.

  The encrypt option is not supported on connections that are using SqlClient.

  Important

  The ODBC encrypt function does not provide encryption. You should not rely on this function to protect passwords that are transmitted over a network. If this information will be transmitted across a network, use SSL or IPSec.

• @encrypt = 'none'
  Specifies that no obfuscation be used. The password is passed to SQL Server as plain text. This is the default.

• @encrypt= 'odbc'
  Specifies that ODBC will obfuscate the password by using the ODBC encrypt function before sending the password to the SQL Server Database Engine. This can be specified only when you are using either an ODBC client or the OLE DB Provider for SQL Server.

• [ @fCreateCookie = ] true | false
  Specifies whether a cookie is to be created. true is implicitly converted to 1. false is implicitly converted to 0.

• [ @cookie = ] @cookie OUTPUT
  Specifies an output parameter to contain the cookie. The cookie is generated only if the value of @fCreateCookie is true. varbinary(8000)

  Note

  The cookie OUTPUT parameter for sp_setapprole is currently documented as varbinary(8000) which is the correct maximum length. However the current implementation returns varbinary(50). Applications should continue to reserve varbinary(8000) so that the application continues to operate correctly if the cookie return size increases in a future release.

Return Code Values

0 (success) and 1 (failure)

Remarks

After an application role is activated by using sp_setapprole, the role remains active until the user either disconnects from the server or executes sp_unsetapprole. sp_setapprole can be executed only by direct Transact-SQL statements. sp_setapprole cannot be executed within another stored procedure or within a user-defined transaction.

For an overview of application roles, see Application Roles.

Security Note

To protect the application role password when it is transmitted across a network, you should always use an encrypted connection when enabling an application role.. The Microsoft ODBC encrypt option is not supported by SqlClient. If you must store credentials, encrypt them with the crypto API functions. The parameter password is stored as a one-way hash. To preserve compatibility with earlier versions of SQL Server, password complexity policy is not enforced by sp_addapprole. To enforce password complexity policy, use CREATE APPLICATION ROLE.

Permissions

Requires membership in public and knowledge of the password for the role.

Examples

A. Activating an application role without the encrypt option

The following example activates an application role named SalesAppRole, with the plain-text password AsDeF00MbXX, created with permissions specifically designed for the application used by the current user.

EXEC sp_setapprole 'SalesApprole', 'AsDeF00MbXX';
GO

B. Activating an application role with a cookie and then reverting to the original context

The following example activates the Sales11 application role with password fdsd896#gfdbfdkjgh700mM, and creates a cookie. The example returns the name of the current user, and then reverts to the original context by executing sp_unsetapprole.

DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'
    , @fCreateCookie = true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- This will return the name of the application role, Sales11.
EXEC sp_unsetapprole @cookie;
-- The application role is no longer active.
-- The original context has now been restored.
GO
SELECT USER_NAME();
-- This will return the name of the original user. 
GO 

See Also

Reference

System Stored Procedures (Transact-SQL)

Security Stored Procedures (Transact-SQL)

CREATE APPLICATION ROLE (Transact-SQL)

DROP APPLICATION ROLE (Transact-SQL)

sp_unsetapprole (Transact-SQL)