sp-unsetapprole-transact-sql.md

title	sp_unsetapprole (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	06/10/2016
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sp_unsetapprole_TSQL sp_unsetapprole
dev_langs	TSQL
helpviewer_keywords	sp_unsetapprole
ms.assetid	4c4033d3-1a34-4dfb-835d-e3293d1a442d
caps.latest.revision	14
author	BYHAM
ms.author	rickbyh
manager	jhubbard

sp_unsetapprole (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

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

Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version).

Transact-SQL Syntax Conventions

Syntax

  
sp_unsetapprole @cookie   

Arguments

@cookie
Specifies the cookie that was created when the application role was activated. The cookie is created by sp_setapprole (Transact-SQL). varbinary(8000).

Note

The cookie 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)

Remarks

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.

Permissions

Requires membership in public and knowledge of the cookie saved when the application role was activated.

Examples

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   

See Also

sp_setapprole (Transact-SQL)
System Stored Procedures (Transact-SQL)
Security Stored Procedures (Transact-SQL)
CREATE APPLICATION ROLE (Transact-SQL)
DROP APPLICATION ROLE (Transact-SQL)