Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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