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