Deactivates an application role and reverts to the previous security context.
Applies to: SQL Server (SQL Server 2008 through current version).
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.
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. 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