sp_setapprole (Transact-SQL)sp_setapprole (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

激活与当前数据库中的应用程序角色关联的权限。Activates the permissions associated with an application role in the current database.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


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


[ @rolename = ] 'role'当前数据库中定义的应用程序角色的名称。[ @rolename = ] 'role' Is the name of the application role defined in the current database. role的值为sysname,无默认值。role is sysname, with no default. 角色必须存在于当前数据库中。role must exist in the current database.

[ @password = ] { encrypt N'password' }激活应用程序角色所需的密码。[ @password = ] { encrypt N'password' } Is the password required to activate the application role. password的值为sysname,无默认值。password is sysname, with no default. 可使用 ODBC加密函数对密码进行模糊处理。password can be obfuscated by using the ODBC encrypt function. 当使用加密函数时,必须通过将N置于第一个引号之前,将密码转换为 Unicode 字符串。When you use the encrypt function, the password must be converted to a Unicode string by placing N before the first quotation mark.

使用SqlClient的连接不支持加密选项。The encrypt option is not supported on connections that are using SqlClient.


ODBC encryption函数不提供加密。The ODBC encrypt function does not provide encryption. 您不应当依赖该函数来保护通过网络传输的密码。You should not rely on this function to protect passwords that are transmitted over a network. 如果通过网络传输该信息,则使用 SSL 或者 IPSec。If this information will be transmitted across a network, use SSL or IPSec.

@encrypt= "none"@encrypt = 'none'
指定不使用任何模糊代码。Specifies that no obfuscation be used. 密码以明文形式传递到 SQL ServerSQL ServerThe password is passed to SQL ServerSQL Server as plain text. 这是默认值。This is the default.

@encrypt= "odbc"@encrypt= 'odbc'
指定在将密码发送到之前,ODBC 将使用 ODBC encrypt函数来模糊处理密码SQL Server 数据库引擎SQL Server Database EngineSpecifies that ODBC will obfuscate the password by using the ODBC encrypt function before sending the password to the SQL Server 数据库引擎SQL Server Database Engine. 这只能在使用 ODBC 客户端或 OLE DB Provider for SQL Server 时指定。This can be specified only when you are using either an ODBC client or the OLE DB Provider for SQL Server.

[ @fCreateCookie = ] true | false指定是否要创建 cookie。[ @fCreateCookie = ] true | false Specifies whether a cookie is to be created. true将隐式转换为1。true is implicitly converted to 1. false将隐式转换为0。false is implicitly converted to 0.

[ @cookie = ] @cookie OUTPUT指定包含 cookie 的输出参数。[ @cookie = ] @cookie OUTPUT Specifies an output parameter to contain the cookie. 仅当** @fCreateCookie的值为true**时,才生成 cookie。The cookie is generated only if the value of @fCreateCookie is true. varbinary (8000)varbinary(8000)


**sp_setapprole** 的 cookie **OUTPUT** 参数现记载为 **varbinary(8000)** ,这是正确的最大长度。The cookie OUTPUT parameter for sp_setapprole is currently documented as varbinary(8000) which is the correct maximum length. 但是,目前执行返回 varbinary(50)However the current implementation returns varbinary(50). 应用程序应继续保留varbinary (8000) ,这样,如果 cookie 在将来的版本中返回大小增加,应用程序将继续正常运行。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(成功)和1(失败)0 (success) and 1 (failure)


使用sp_setapprole激活应用程序角色后,该角色将保持活动状态,直到用户从服务器断开连接或执行sp_unsetapproleAfter 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只能由直接Transact-SQLTransact-SQL语句执行。sp_setapprole can be executed only by direct Transact-SQLTransact-SQL statements. 不能在另一个存储过程或用户定义的事务中执行sp_setapprolesp_setapprole cannot be executed within another stored procedure or within a user-defined transaction.

有关应用程序角色的概述,请参阅应用程序角色For an overview of application roles, see Application Roles.


若要在通过网络传输应用程序角色密码时对其进行保护,在启用应用程序角色时,应始终使用加密连接。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. MicrosoftMicrosoft SqlClient不支持 ODBC encrypt选项。The MicrosoftMicrosoft ODBC encrypt option is not supported by SqlClient. 如果必须存储凭据,请使用加密 API 函数对这些凭据进行加密。If you must store credentials, encrypt them with the crypto API functions. 参数password作为单向哈希进行存储。The parameter password is stored as a one-way hash. 为了保持与早期版本的SQL ServerSQL Server兼容性,不会sp_addapprole强制密码复杂性策略。To preserve compatibility with earlier versions of SQL ServerSQL 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.A. 激活应用程序角色但不使用加密选项Activating an application role without the encrypt option

以下示例使用明文密码 SalesAppRole 激活名为 AsDeF00MbXX 的应用程序角色,该密码是使用特别为当前用户使用的应用程序设计的权限创建的。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 sys.sp_setapprole 'SalesApprole', 'AsDeF00MbXX';  

以下示例使用密码 Sales11 激活 fdsd896#gfdbfdkjgh700mM 应用程序角色并创建一个 cookie。The following example activates the Sales11 application role with password fdsd896#gfdbfdkjgh700mM, and creates a cookie. 该示例返回当前用户的名称,然后通过执行 sp_unsetapprole 恢复到原始上下文中。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 sys.sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'  
    , @fCreateCookie = true, @cookie = @cookie OUTPUT;  
-- The application role is now active.  
-- This will return the name of the application role, Sales11.  
EXEC sys.sp_unsetapprole @cookie;  
-- The application role is no longer active.  
-- The original context has now been restored.  
-- This will return the name of the original user.

另请参阅See Also

系统存储过程 (transact-sql) 安全存储过程 (TRANSACT-SQL) CREATE application ROLE ( Transact-sql)DROP Application role (transact-sql)sp_unsetapprole (transact-sql)System Stored Procedures (Transact-SQL) Security Stored Procedures (Transact-SQL) CREATE APPLICATION ROLE (Transact-SQL) DROP APPLICATION ROLE (Transact-SQL) sp_unsetapprole (Transact-SQL)