create procedure sys.sp_MSdropmergepalrole(@pubid uniqueidentifier = NULL)
AS
    declare @retcode int

    if (@pubid is NULL)
    begin
    	declare hC1 CURSOR LOCAL FAST_FORWARD FOR select pubid FROM dbo.sysmergepublications FOR READ ONLY
    	
    	OPEN hC1
    	FETCH hC1 INTO @pubid
    	WHILE (@@fetch_status <> -1)
    		BEGIN
    			EXECUTE @retcode = sys.sp_MSdropmergepalrole @pubid
    			if @retcode<>0 or @@error<>0
    			    return 1
    			FETCH hC1 INTO @pubid
    		END
    	CLOSE hC1
    	DEALLOCATE hC1
    end

    -- drop the database role that contains users who have access to the publication
    -- this has to be done outside the transaction since role operations cannot be
    -- done in transaction
    declare @role sysname
    declare @member sysname

    select @role = sys.fn_MSmerge_GetPALRole(@pubid)

    if exists (select * from sys.database_principals where name=@role and type = 'R')
    begin
        -- this role is a memeber of the database pal role. Drop it from there.
        if exists (select * from sys.database_principals where name='MSmerge_PAL_role' and type = 'R')
        begin
            exec @retcode = dbo.sp_droprolemember 'MSmerge_PAL_role', @role
            if (@retcode <> 0 or @@error <> 0)
                return 1
        end

        declare role_members cursor fast_forward
        for select u.name as MemberName
    			from sys.database_principals u, sys.database_principals g, sys.database_role_members m
    			where g.name = @role
    				and g.principal_id = m.role_principal_id
    				and g.type = 'R'
    				and u.principal_id = m.member_principal_id
    				
        open role_members
        fetch role_members into @member
        while @@fetch_status <> -1
        begin
            exec @retcode = sys.sp_droprolemember @role, @member
            if (@retcode <> 0 or @@error <> 0)
            BEGIN
                close role_members
                deallocate role_members
                RAISERROR (14005, 16, -1)
                RETURN (1)
            END
            fetch role_members into @member
        end
        close role_members
        deallocate role_members

        exec @retcode = sys.sp_droprole @role
        if (@retcode <> 0 or @@error <> 0)
        BEGIN
            RAISERROR (14005, 16, -1)
            RETURN (1)
        END
    end