sp_unsetapprole (Transact-SQL)
SQL Server 2005
New:
14 April 2006
Deactivates an application role and reverts to the previous security context.
Transact-SQL Syntax Conventions
- @cookie
-
Specifies the cookie that was created when the application role was activated. The cookie is created by sp_setapprole (Transact-SQL). varbinary(8000).
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
