sp_unsetapprole (Transact-SQL)

Deactivates an application role and reverts to the previous security context.

Topic link iconTransact-SQL Syntax Conventions



Specifies the cookie that was created when the application role was activated. The cookie is created by sp_setapprole (Transact-SQL). 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.

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.

Activating 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 sp_unsetapprole.

DECLARE @cookie varbinary(8000);
EXEC 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 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.