Development/MsSql

LINKED SERVERS SUMMARY

@위너스 2010. 8. 6. 14:06
  1. -- Script to get all the info from the Linked Server properties
  2. -- Also extract the ALIAS from the client network utility
  3. -- Also extract the DNS IP from the Alias
  4. USE master
  5. go
  6.  
  7. IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_detail')
  8. DROP TABLE tempdb..linkedsvr_detail
  9. IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_summary')
  10. DROP TABLE tempdb..linkedsvr_summary
  11. IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'linkedsvr_dns')
  12. DROP TABLE tempdb..linkedsvr_dns
  13. go
  14.  
  15. -- SQL Client Network Alias
  16. CREATE TABLE #alias(Alias nvarchar(50), Server nvarchar(250))
  17. INSERT INTO #alias exec master..xp_regenumvalues 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
  18. UPDATE #alias set Server = substring(Server, charindex(',', Server)+1, 100)
  19.  
  20. SELECT DISTINCT @@servername FromServer, s.srvname LinkedServerName, a.server alias, '255.255.255.255' ip, isnull(a.server, datasource) rmtserver, u.rmtloginame
  21. INTO tempdb..linkedsvr_summary
  22. FROM sysservers s
  23. JOIN sysoledbusers u ON s.srvid = rmtsrvid
  24. LEFT JOIN #alias a on a.alias = s.datasource
  25. WHERE s.isremote = 1 AND u.rmtloginame IS NOT NULL
  26. ORDER BY 2,3
  27.  
  28. SELECT @@servername FromServer, s.srvname LinkedServerName, datasource, a.server alias, u.rmtloginame, l.loginname
  29. INTO tempdb..linkedsvr_detail
  30. FROM sysservers s
  31. JOIN sysoledbusers u ON s.srvid = rmtsrvid
  32. JOIN syslogins l ON u.loginsid = l.sid
  33. LEFT JOIN #alias a on a.alias = s.datasource
  34. WHERE s.isremote = 1
  35. ORDER BY s.srvname, u.rmtloginame
  36.  
  37. -- DNS Alias Entry
  38. CREATE TABLE #dns(alias nvarchar(50) primary key, ip varchar(15))
  39.  
  40. INSERT INTO #dns(alias)
  41. SELECT DISTINCT case when charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end
  42. from tempdb..linkedsvr_summary
  43.  
  44. create table #output(output nvarchar(255)) -- shell cmd output
  45.  
  46. declare @alias nvarchar(50), @cmd nvarchar(250), @ip varchar(15)
  47. declare ALIASCUR cursor read_only fast_forward
  48. for select alias from #dns
  49.  
  50. open ALIASCUR
  51. fetch next from ALIASCUR into @alias
  52. while @@fetch_status = 0
  53. begin
  54. set @cmd = 'PING -n 1 ' + @alias
  55. insert into #output exec master..xp_cmdshell @cmd
  56. select @ip = substring(output, charindex('[',output)+1,charindex(']',output)-1-charindex('[',output))
  57. from #output where left(output,7)='Pinging'
  58.  
  59. update #dns set ip = isnull(@ip,'DNS error') where alias = @alias
  60. fetch next from ALIASCUR into @alias
  61. end
  62. close ALIASCUR
  63. deallocate ALIASCUR
  64.  
  65. update s
  66. set ip = d.ip
  67. from tempdb..linkedsvr_summary s
  68. left join #dns d on case when charindex('\',rmtserver)>0 then left(rmtserver,charindex('\',rmtserver)-1) else rmtserver end = d.alias
  69.  
  70. select * into tempdb..linkedsvr_dns from #dns -- just for backups
  71.  
  72. drop table #dns
  73. drop table #output
  74. drop table #alias
  75.  
  76. -- Now you need to gather info from all servers and see if we have SA login used by Linked Servers
  77.  
  78. -- select * from tempdb..linkedsvr_summary
  79. -- select * from tempdb..linkedsvr_detail
  80. -- select * from tempdb..linkedsvr_dns
  81. -- select @@servername, lower(name) from master..syslogins where sysadmin = 1
  82.  
  83. -- select fromserver, [to] LinkedServerName, l.Login
  84. -- from dbo.LinkedServers s
  85. -- join sa_logins l on l.Server=s.toserver and l.Login=s.login
  86. -- order by 1,2