Applies to: SQL Server
Deactivates an application role and reverts to the previous security context.
sp_unsetapprole [ @cookie = ] cookie [ ; ]
[ @cookie = ] cookie
Specifies the cookie that was created when the application role was activated. @cookie is varbinary(8000), with no default. The cookie is created by sp_setapprole (Transact-SQL).
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)
After an application role is activated by using
sp_setapprole, the role remains active until the user either disconnects from the server or executes
For an overview of application roles, see Application Roles.
Requires membership in public and knowledge of the cookie saved when the application role was activated.
Activate an application role with a cookie, then reverting to the previous 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
DECLARE @cookie VARBINARY(8000); EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM', @fCreateCookie = true, @cookie = @cookie OUTPUT; -- The application role is now active. SELECT USER_NAME(); -- 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 -- Return the name of the original user. SELECT USER_NAME(); GO