sp_setapprole (Transact-SQL)

[ @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)

0 (success) and 1 (failure)

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.

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

A. Activating an application role without the encrypt option

EXEC sp_setapprole 'SalesApprole', 'AsDeF00MbXX';
GO

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

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 

Reference

Other Resources