Script out all linked/remote servers

, 2002-12-05

T-SQL which scripts out all linked and remote servers.  Works on SQL 7.0 and 2000.  Remote login password is only decrypted on SQL 7.0.

--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

Rate

5 (8)

Share

Share

Rate

5 (8)

Related content

What is the Maximum Page Size in SQL Server 2000?

I have always read that 8060 bytes is the maximum size. This is stated over and over again in Books Online, the MS site and numerous other sites, including this one. However a post in our forum recently questioned this. I decided to verify the problem and do a little research.

4.55 (11)

2006-05-19 (first published: )

36,458 reads

SQL Server 2000 User-Defined Functions White Paper

The User_Defined_Functions.exe file contains the User-Defined Functions white paper. The User-Defined functions white paper outlines the characteristics of the new user-defined function (UDF) feature that is introduced in Microsoft SQL Server 2000. The white paper also summarizes how you can create your own Transact-SQL functions to extend the programmability of Transact-SQL.

2001-08-31

2,233 reads

Work Around Errors in Database Web Apps

ne of the issues you face when building Web applications is handling the errors you encounter when interacting with a back-end database. I was recently working with someone to create a new Web site with SQL Server™, ActiveX® Data Objects (ADO), and ASP. Lots of little things came up that I thought were worth sharing with MIND readers, so I'll focus this column on what I learned from this experience and the solutions to many of the problems I faced.

2001-07-13

1,579 reads