Linked Servers Summary

-- Script to get all the info from the Linked Server properties
-- Also extract the ALIAS from the client network utility
-- Also extract the DNS IP from the Alias
USE master
go
 
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_detail')
DROP TABLE tempdb..linkedsvr_detail
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_summary')
DROP TABLE tempdb..linkedsvr_summary
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_dns')
DROP TABLE tempdb..linkedsvr_dns
go
 
-- SQL Client Network Alias
CREATE TABLE #alias(Alias nvarchar(50), Server nvarchar(250))
INSERT INTO #alias exec master..xp_regenumvalues 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
UPDATE #alias set Server = substring(Server, charindex(',', Server)+1, 100)
 
SELECT DISTINCT @@servername FromServer, s.srvname LinkedServerName, a.server alias, '255.255.255.255' ip, isnull(a.server, datasource) rmtserver, u.rmtloginame
INTO tempdb..linkedsvr_summary
FROM sysservers s
JOIN sysoledbusers u ON s.srvid = rmtsrvid
LEFT JOIN #alias a on a.alias = s.datasource
WHERE s.isremote = 1 AND u.rmtloginame IS NOT NULL
ORDER BY 2,3
 
SELECT @@servername FromServer, s.srvname LinkedServerName, datasource, a.server alias, u.rmtloginame, l.loginname
INTO tempdb..linkedsvr_detail
FROM sysservers s
JOIN sysoledbusers u ON s.srvid = rmtsrvid
JOIN syslogins l ON u.loginsid = l.sid
LEFT JOIN #alias a on a.alias = s.datasource
WHERE s.isremote = 1 
ORDER BY s.srvname, u.rmtloginame
 
-- DNS Alias Entry
CREATE TABLE #dns(alias nvarchar(50) primary key, ip varchar(15))
 
INSERT INTO #dns(alias)
SELECT DISTINCT case when charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end 
from tempdb..linkedsvr_summary
 
create table #output(output nvarchar(255)) -- shell cmd output
 
declare @alias nvarchar(50), @cmd nvarchar(250), @ip varchar(15)
declare ALIASCUR cursor read_only fast_forward
for select alias from #dns
 
open ALIASCUR
fetch next from ALIASCUR into @alias
while @@fetch_status = 0
begin
set @cmd = 'PING -n 1 ' + @alias
insert into #output exec master..xp_cmdshell @cmd
select @ip = substring(output, charindex('[',output)+1,charindex(']',output)-1-charindex('[',output))
from #output where left(output,7)='Pinging'
 
update #dns set ip = isnull(@ip,'DNS error') where alias = @alias
fetch next from ALIASCUR into @alias
end
close ALIASCUR
deallocate ALIASCUR
 
update s
set ip = d.ip
from tempdb..linkedsvr_summary s
left join #dns d on case when charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end = d.alias
 
select * into tempdb..linkedsvr_dns from #dns -- just for backups
 
drop table #dns
drop table #output
drop table #alias
 
-- Now you need to gather info from all servers and see if we have SA login used by Linked Servers
 
-- select * from tempdb..linkedsvr_summary
-- select * from tempdb..linkedsvr_detail
-- select * from tempdb..linkedsvr_dns
-- select @@servername, lower(name) from master..syslogins where sysadmin = 1
 
-- select fromserver, [to] LinkedServerName, l.Login
-- from dbo.LinkedServers s 
-- join sa_logins l on l.Server=s.toserver and l.Login=s.login
-- order by 1,2