Script out all linked/remote servers

--Script to script out all linked/remote servers
--Works on 7.0 and 2000 servers
--remote password decrypt only works on 7.0

declare
    @status    smallint,     -- server status
    @server         sysname,         -- server name
    @srvid    smallint,     -- server id
    @srvproduct     nvarchar(128),   -- product name (dflt to ss)
    @allsetopt      int,     --sum of all settable options
    @provider       nvarchar(128),   -- oledb provider name
    @datasrc        nvarchar(4000),  -- oledb datasource property
    @location       nvarchar(4000),  -- oledb location property
    @provstr        nvarchar(4000),  -- oledb provider-string property
    @catalog        sysname,         -- oledb catalog property
    @netname    varchar(30),     -- Server net name
    @srvoption    varchar(30),     -- server options
    @loclogin       varchar(30),     -- Local user
    @rmtlogin       varchar(30),     -- Remote user
    @selfstatus     smallint,     -- linked server login status
    @rmtpass        varbinary(256),  -- linked server login password
    @pwdtext        nvarchar(128),   -- linked server decrypted password
    @i              int,             -- linked server pswd decrypt var
    @lsb            tinyint,         -- linked server pswd decrypt var
    @msb            tinyint,         -- linked server pswd decrypt var
    @tmp            varbinary(256)   -- linked server pswd decrypt var

select @allsetopt=number from master.dbo.spt_values
where type = 'A' and name = 'ALL SETTABLE OPTIONS'  -- Only 7.0 else use 4063
    
declare d cursor for SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource,
location, providerstring, catalog, srvnetname 
from master..sysservers
where srvid > 0  -- Local Server
open d   
   fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc,
@location, @provstr, @catalog, @netname
SET NOCOUNT  ON

while (@@FETCH_STATUS<>-1) begin
   
PRINT '--------------------------------'
Print '--      ' + @server
PRINT '--------------------------------'   
If @status in (64,65)               --Remote Server
Begin
Print 'sp_addserver'
Print '  @server = '''+ @server + ''''
Print '  GO'

If @status = 64
Begin
Print 'sp_serveroption'
Print '  @server = '''+ @server + ''','
Print '  @optname = ''rpc'','
Print '  @optvalue = ''false'''
Print '  GO'
End
exec ('declare  r cursor for
select l.name, r.remoteusername from 
sysremotelogins r join sysservers s on
r.remoteserverid = s.srvid
join syslogins l on
r.sid = l.sid
where s.srvname = '''+ @server + '''')
open r
fetch next from r into @loclogin, @rmtlogin
while (@@FETCH_STATUS<>-1)
begin
Print 'sp_addremotelogin'
Print '  @remoteserver = '''+ @server + ''','
Print '  @loginame  = '''+ @loclogin + ''','
Print '  @remotename = '''+ @rmtlogin + ''''
Print '  GO'
fetch next from r into @loclogin, @rmtlogin
end
close r
deallocate r


End
Else   --Linked server
Begin
If exists (select * from tempdb..sysobjects where name like '#tmpsrvoption%')
Begin
drop table #tmpsrvoption
End

Create Table #tmpsrvoption
(
srvoption  varchar(30)
)
insert #tmpsrvoption
select v.name
from master.dbo.spt_values v, master.dbo.sysservers s
where srvid = @srvid
and (v.number & s.srvstatus)=v.number
and (v.number & isnull(@allsetopt,4063)) <> 0 
and v.number not in (-1, isnull(@allsetopt,4063))
and v.type = 'A'

PRINT 'sp_addlinkedserver'
Print '  @server = '''+ @server + ''''
Print ',  @srvproduct = ''' + @srvproduct + ''''
If @srvproduct <> 'SQL Server'   --Cannot specify additional info for SQL Server Product
Begin
Print ',  @provider = ''' + @provider + ''''
Print ',  @datasrc = ''' + @datasrc + ''''
Print ',  @location = ''' + @location + ''''
Print ',  @provstr = ''' + @provstr + '''' 
Print ',  @catalog = ''' + @catalog + ''''
End
Print '  GO'
   
-- Set all servers options to false, then reset correct server options
Print 'sp_serveroption'
Print '  @server = '''+ @server + ''','
Print '  @optname = ''rpc'','
Print '  @optvalue = ''false'''
Print '  GO'
Print 'sp_serveroption'
Print '  @server = '''+ @server + ''','
Print '  @optname = ''rpc out'','
Print '  @optvalue = ''false'''
Print '  GO'
Print 'sp_serveroption'
Print '  @server = '''+ @server + ''','
Print '  @optname = ''data access'','
Print '  @optvalue = ''false'''
Print '  GO'

declare s cursor for SELECT srvoption
from #tmpsrvoption

open s  
   fetch next from s into @srvoption

while (@@FETCH_STATUS<>-1)
begin
Print 'sp_serveroption'
Print '  @server = '''+ @server + ''','
Print '  @optname = '''+ @srvoption + ''','
Print '  @optvalue = ''true'''
Print '  GO'

fetch next from s into @srvoption
End
close s
deallocate s


--Script linked server logins
If exists (select * from tempdb..sysobjects where name like '#tmplink%')
Begin
drop table #tmplink
End

create table #tmplink
(
rmtserver sysname,
loclogin sysname null,
selfstatus smallint,
rmtlogin sysname null
)

insert #tmplink
exec ('sp_helplinkedsrvlogin '''+ @server + '''')

declare ll cursor for
select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin

open ll
fetch next from ll into @loclogin, @selfstatus, @rmtlogin

while (@@FETCH_STATUS<>-1)
begin
    -- Use self no remote user/password
If (@selfstatus = 1 and @loclogin is null)
Begin
Print 'sp_addlinkedsrvlogin'
Print '  @rmtsrvname = '''+ @server + ''','
Print '  @useself = ''true'''
Print '  GO'
End
Else
If (@selfstatus = 1 and @loclogin is not null) Begin
Print 'sp_addlinkedsrvlogin'
Print '  @rmtsrvname = '''+ @server + ''','
Print '  @useself = ''true'','
Print '  @locallogin = '''+ @loclogin + ''','
Print '  @rmtuser = NULL,'
Print '  @rmtpassword = NULL'
Print '  GO'
End
Else
If (@selfstatus = 0 and @rmtlogin is null) Begin
Print 'sp_addlinkedsrvlogin'
Print '  @rmtsrvname = '''+ @server + ''','
Print '  @useself = ''false'','
Print '  @locallogin = NULL,'
Print '  @rmtuser = NULL,'
Print '  @rmtpassword = NULL'
Print '  GO'
End
Else
If (@selfstatus = 0) Begin  -- Check for Use self mappings
exec ('declare pwd cursor for
select l.password from master..sysservers s
join master..sysxlogins l on s.srvid = l.srvid --where l.sid is not null
where s.srvname = '''+ @server + ''' and l.name = '''+ @rmtlogin + '''')
-- Decrypt passwords
-- Only works for 7.0 server
-- Encrypt algorithm changed in 2000
open pwd
fetch next from pwd into @rmtpass
while @@fetch_status = 0
    begin
    set @i = 0
    set @pwdtext = N''
    while @i < datalength(@rmtpass)
        begin
        set @tmp = encrypt(@pwdtext + nchar(0))
        set @lsb = convert(tinyint, substring(@tmp, @i + 1, 1))
            ^ convert(tinyint, substring(@rmtpass, @i + 1, 1))
        set @i = @i + 1

        set @tmp = encrypt(@pwdtext + nchar(@lsb))
        set @msb = convert(tinyint, substring(@tmp, @i + 1, 1))
            ^ convert(tinyint, substring(@rmtpass, @i + 1, 1))
        set @i = @i + 1

        set @pwdtext = @pwdtext + nchar(convert(smallint, @lsb)
            + 256 * convert(smallint, @msb))
        end

    Print 'sp_addlinkedsrvlogin'
Print '  @rmtsrvname = '''+ @server + ''','
Print '  @useself = ''false'','
If (@loclogin is null)
Begin
Print '  @locallogin = NULL,'
End
Else
Begin
Print '  @locallogin = '''+ @loclogin + ''','
End
If (@rmtlogin is null)
Begin
Print '  @rmtuser = NULL,'
End
Else
Begin
Print '  @rmtuser = '''+ @rmtlogin + ''','
End
If (@pwdtext is null)
Begin
Print '  @rmtpassword = NULL'
End
Else
Begin
print '  @rmtpassword = '''+ @pwdtext + ''''
End
Print '  GO'

    fetch next from pwd into @rmtpass
    end
    close pwd
    deallocate pwd
End
fetch next from ll into @loclogin, @selfstatus, @rmtlogin

End
close ll
deallocate ll



End
If @netname <> @server   -- If the srvnetname.sysservers is different from srvname.sysservers
Begin
Print 'sp_setnetname'
Print '  @server = '''+ @server + ''','
Print '  @network_name = '''+ @netname + ''''
End

fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc,
@location, @provstr, @catalog, @netname

End
close d
deallocate d