create procedure sys.sp_unlink_publication_internal
(
    @publisher sysname         -- publishing server name
    ,@publisher_db sysname    -- publishing database name
    ,@publication sysname      -- publication name
    ,@reserved nvarchar(10)=null -- 'drop all for all subscriptions'
    ,@publisherlinkusertodrop sysname output -- name of the publisher link user to drop after this procedure completes (null if there is no need to drop any publisher link user)
)
as
begin
    set nocount on
    declare @retcode int
            ,@publisherlink sysname
            ,@publisherlinkuser sysname
            ,@rpcproc nvarchar(4000)
            ,@security_mode int
            ,@islocalpub bit
            ,@modedropall nvarchar(10)
            ,@certname sysname

    
    -- initialize
    
    select @publisher = upper(@publisher)
        ,@reserved = lower(@reserved)
        ,@islocalpub = case when (upper(@publisher) = upper(publishingservername())) then 1 else 0 end
        ,@modedropall = N'drop_all'
    
    -- check if we need to unlink
    
    if object_id('MSsubscription_properties') is not null
    begin
        if (@reserved = @modedropall)
        begin
            
            -- all subscriptions
            -- unlink each subscription recursively
            
            declare #hcsubprop cursor local fast_forward for
                select distinct publisher, publisher_db, publication
                from dbo.MSsubscription_properties
            open #hcsubprop
            fetch #hcsubprop into @publisher, @publisher_db, @publication
            while (@@fetch_status != -1)
            begin
                exec @retcode = sys.sp_unlink_publication @publisher, @publisher_db, @publication
                if (@retcode != 0 or @@error != 0)
                    return (1)
                fetch #hcsubprop into @publisher, @publisher_db, @publication
            end
            close #hcsubprop
            deallocate #hcsubprop
        end -- @reserved = @modedropall
        else
        begin -- @reserved != @modedropall
            
            -- specific subscription
            -- Check if we need to do any processing
            
            if object_id('MSsubscription_properties') is null
                return 0
            if not exists (select * from sys.columns where
                                object_id = object_id('MSsubscription_properties')
                                and name in ('publisherlink', 'publisherlinkuser'))
                return 0
            
            -- check for publisher link related information
            
            select @publisherlink = case when (publisher_security_mode in (0,1))
                            then sys.fn_MSrepllinkname(N'REPLLINK', upper(@publisher), @publisher_db, @publication, db_name())
                            else publisherlink end
                ,@publisherlinkuser = case when (publisher_security_mode in (0,1) and publisherlinkuser = N'xxxx')
                            then N'repllinkproxy' else publisherlinkuser end
                ,@security_mode = publisher_security_mode
            from dbo.MSsubscription_properties
            where upper(publisher) = @publisher
                and    publisher_db = @publisher_db
                and    publication = @publication

            if (@publisherlink is not null)
            begin
                
                -- skip this processing for cases
                -- where MSreplication_objects does not exist
                -- or we have an older version of MSreplication_objects
                
                if object_id('MSreplication_objects') is null
                    return 0
                if not exists (select * from sys.columns where
                                    object_id = object_id('MSreplication_objects')
                                    and name = 'publication')
                    return 0
                
                -- we have link information
                -- check if we need to remove certificates
                
                select distinct @certname = c.name
                from sys.crypt_properties cp join sys.certificates c
                    on cp.thumbprint = c.thumbprint
                where object_name(cp.major_id) in
                    (select object_name
                        from dbo.MSreplication_objects
                        where upper(publisher) = @publisher
                            and publisher_db = @publisher_db
                            and publication = @publication
                            and object_type = 'T')
                if (@certname is not null)
                begin
                    
                    -- remove cert grant from target db
                    
                    if (@islocalpub = 0)
                    begin
                        select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
                        exec @rpcproc @mode = 2
                                            ,@islocalpub = @islocalpub
                                            ,@certname = @certname
                                            ,@targetdb = N'master'
                    end
                    else
                    begin
                        select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
                        exec @rpcproc @mode = 2
                                            ,@islocalpub = @islocalpub
                                            ,@certname = @certname
                                            ,@targetdb = @publisher_db
                    end
                    
                    -- remove cert from subscriber db
                    
                    exec sys.sp_MSrepltrigpreparecert @mode = 2
                                                ,@certname = @certname
                                                ,@publisher = @publisher
                                                ,@publisher_db = @publisher_db
                                                ,@publication = @publication
                end

                if (@islocalpub = 0 and @security_mode in (0,1))
                begin
                    
                    -- remote publisher - drop unique link
                    
                    exec @retcode = sys.sp_MSprocesspublisherlink @mode = 2
                        ,@islocalpub = @islocalpub, @securitymode = @security_mode
                        ,@linkname = @publisherlink, @remserver = @publisher
                    if @@error <> 0 or @retcode <> 0
                        return (1)
                end
                if (@security_mode = 0 and @publisherlinkuser is not null )
                begin
                    
                    -- remove context user if no other subscriptions are using it
                    
                    if not exists (select *
                        from dbo.MSsubscription_properties
                        where publisherlinkuser = case when (@publisherlinkuser = N'repllinkproxy') then N'xxxx' else @publisherlinkuser end
                            and (upper(publisher) != @publisher
                                or publisher_db != @publisher_db
                                or publication != @publication))
                    begin
                        if exists (select * from sys.database_principals where name = @publisherlinkuser)
                        begin
                            set @publisherlinkusertodrop = @publisherlinkuser
                        end
                    end
                end
                
                -- update this entry in MSsubscription_properties
                
                update MSsubscription_properties
      set publisherlink = null
                    ,publisherlinkuser = null
                where upper(publisher) = @publisher
                    and publisher_db =  @publisher_db
                    and publication = @publication
                if (@@error <> 0)
                    return (1)
            end -- if (@publisherlink is not null)
        end -- @reserved != @modedropall
    end -- if object_id('MSsubscription_properties') is not null
    
    -- all done
    
    return 0
end