create procedure sys.sp_MSprocesspublisherlink
(
    @mode tinyint                            -- 1 = create/refresh, 2 = drop
    ,@islocalpub bit                          -- 1 = local, 0 = remote
    ,@securitymode tinyint                -- 0 = standard, 1 = integrated
    ,@linkname sysname = null         -- name of the linked server
    ,@remserver sysname = null       -- remote server name
    ,@remlogin sysname = null          -- remote login. Not needed for dropping
    ,@rempassword sysname = null   -- remote password. Not needed for dropping
)
with execute as 'dbo'
as
begin
    set nocount on
    declare @retcode int = 0
                ,@localproxy sysname
                ,@localpwd sysname
                ,@cmd nvarchar(4000)

    
    -- validate @mode
    
    if (@mode not in (1,2))
    begin
        raiserror(21055, 16, -1, '@mode','sp_MSprocesspublisherlink')
        return (1)
    end
    
    -- validate @securitymode
    
    if (@securitymode not in (0,1))
    begin
        raiserror(21055, 16, -1, '@securitymode','sp_MSprocesspublisherlink')
        return (1)
    end
    if (@islocalpub = 0) and (@linkname is null or @remserver is null)
        return 1
    
    -- initialize we always will use uppercase entries for replication metadata
    
    select @linkname = upper(@linkname)
            ,@remserver = upper(@remserver)
            ,@localproxy = 'repllinkproxy'
            ,@localpwd = cast(newid() as sysname)
    
    -- processing remote case
    
    if (@islocalpub = 0)
    begin
        
        -- Drop linker server entry if exists
        
        if exists (select * from sys.servers
                where upper(name collate database_default) = @linkname and upper(data_source collate database_default) = @remserver)
        begin
            exec @retcode = sp_dropserver @server = @linkname, @droplogins = 'droplogins'
            if @@error <> 0 or @retcode <> 0
                return(1)
        end
    end
    
    -- create if necessary
    
    if (@mode = 1)
    begin
        if (@islocalpub = 0)
        begin
            
            -- validate remote login for SQL Authentication
            
            if (@securitymode = 0 and @remlogin is null)
            begin
                raiserror(21055, 16, -1, '@remlogin','@securitymode = 0')
                return (1)
            end
            
            -- create the linked server entry
            
            exec @retcode = sp_addlinkedserver @server = @linkname, @srvproduct = 'SQL Server'
            if @@error <> 0 or @retcode <> 0
                return(1)
            
            -- Set the data source name
            
            exec @retcode = sys.sp_setnetname  @linkname, @remserver
            if @@error <> 0 or @retcode <> 0
                goto UNDO
            
            -- set the properties for the linked server so that it can be used by replication
            
            exec @retcode = sys.sp_serveroption @linkname, 'data access', 'true'
            if @@error <> 0 or @retcode <> 0
                goto UNDO
            exec @retcode = sys.sp_serveroption @linkname, 'rpc', 'true'
            if @@error <> 0 or @retcode <> 0
                goto UNDO
            exec @retcode = sys.sp_serveroption @linkname, 'rpc out', 'true'
            if @@error <> 0 or @retcode <> 0
                goto UNDO
            
            -- drop the default login mapping
            
            exec sp_droplinkedsrvlogin @rmtsrvname = @linkname, @locallogin = null
        end  -- if (@islocalpub = 0)
        
        -- Add login mapping for this link now
        -- process according to security mode
        
        if (@securitymode = 0)
        begin
            
            -- Standard SQL authentication
            -- we will always map local proxy login to the remote server login.
            -- This local proxy login is not meant for external use and hence
            -- the password is random.
            
            
            -- create the local proxy login if needed
            
            if not exists (select * from master.dbo.syslogins where loginname = @localproxy)
            begin
                if (@@microsoftversion >= 0x09000389)
                begin
                    select @cmd = N'create login ' + quotename(@localproxy) + N' with password = N''' + @localpwd + N''''
                    exec sp_executesql @cmd
                end
                else
                begin
                    exec @retcode = sys.sp_addlogin @loginame = @localproxy, @passwd = @localpwd
                end
                if @@error <> 0 or @retcode <> 0
                    goto UNDO
            end
            
            -- create the local proxy user if necessary (dbowner group member
            -- since we do not know which tables will be updated)
            
            if exists (select * from sys.database_principals where name = @localproxy)
            begin
                exec @retcode =sys.sp_addrolemember @rolename = 'db_owner'
                                                                        , @membername = @localproxy
            end
            else
            begin
                if (@@microsoftversion >= 0x09000389)
                begin
                    select @cmd = N'create user ' + quotename(@localproxy) + N' for login ' + quotename(@localproxy)
                    exec sp_executesql @cmd
                    -- we also need to add them as a db_owner
                    exec @retcode =sys.sp_addrolemember @rolename = 'db_owner'
                                                                        , @membername = @localproxy
                end
                else
                begin
                    exec @retcode = sys.sp_adduser @loginame = @localproxy
                                                                ,@name_in_db = @localproxy
                                                                ,@grpname = 'db_owner'
                end
            end
            if @@error <> 0 or @retcode <> 0
                goto UNDO

            if (@islocalpub = 0)
            begin
                
                -- map the remote login to the proxy local login
                
                exec @retcode = sp_addlinkedsrvlogin @rmtsrvname = @linkname
                                                            ,@useself = 'false' -- we will use specific remote login
                                                            ,@locallogin = @localproxy -- only this login can use it
                                                            ,@rmtuser = @remlogin
                                                            ,@rmtpassword = @rempassword
                if @@error <> 0 or @retcode <> 0
                    goto UNDO
            end  -- if (@islocalpub = 0)
        end
        else
        begin
            if (@islocalpub = 0)
            begin
                
                -- Integrated authentication
                -- we will always map self login to the remote machine
                
                exec @retcode = sp_addlinkedsrvlogin @rmtsrvname = @linkname
                                                            ,@useself = 'true' -- we will use self credentials
                if @@error <> 0 or @retcode <> 0
                    goto UNDO
            end  -- if (@islocalpub = 0)
        end
    end
    
    -- all done
    
    return 0

UNDO:
    if (@islocalpub = 0)
    begin
        
        -- drop the server if we have created one
        
        if exists (select * from sys.servers
                where upper(name collate database_default) = @linkname and upper(data_source collate database_default) = @remserver)
        begin
            exec sp_dropserver @server = @linkname, @droplogins = 'droplogins'
        end
    end  -- if (@islocalpub = 0)
    
    -- return error
    
    return 1
end