create procedure sp_setapprole
    @rolename   sysname,         -- name app role
 
    @password   sysname,		 -- password for app role
 
	@encrypt	varchar( 10 )	= 'none'	 -- Encryption style (%af_src_str_1 | %af_src_str_2)
 
as
     -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
 
	set nocount on

     -- DISALLOW USER TRANSACTION --
 
	set implicit_transactions off
	if (@@trancount >  0 )
	begin
		raiserror( 15002 ,- 1 ,- 1 ,'sp_setapprole')
		return ( 1 )
	end

	 -- CHECK PARAMETER
 
	if (@rolename IS NULL)
    begin
        raiserror( 15431 ,- 1 ,- 1 )
        return ( 1 )
    end

	 -- VALIDATE ENCRYPTION
 
	declare @encrStyle int
	select @encrStyle = case lower(@encrypt) when 'none' then  0  when 'odbc' then  1  else null end
	if @encrStyle is null
	begin
        raiserror( 15600 ,- 1 ,- 1 ,'sp_setapprole')
        return ( 1 )
	end

     -- SP MUST BE CALLED AT ADHOC LEVEL --
 
    if (@@nestlevel >  1 )
    begin
        raiserror( 15422 ,- 1 ,- 1 )
        return ( 1 )
    end

     -- ACTIVATE APPROLE (THIS IS ONLY VALID FROM THIS SP!) --
 
    setuser @rolename, @password, @encrStyle

     -- RETURN SUCCESS/FAILURE --
 
    if (@@error <>  0 )
        return ( 1 )

	raiserror( 15494 ,- 1 ,- 1 ,@rolename)

    return ( 0 ) -- sp_setapprole