List Linked Server details using query

• sani

  SSCrazy

  Points: 2444

  More actions

  December 8, 2009 at 5:38 am

  #214369

  Hi all,

  I need to create a script from all existing linked server in detail automatically every night but couldn't find and SP or View to have complete information. I tried the following but no use. None of them gave me local login names:

  select * from sys.servers

  select * from sys.linked_logins

  select * from sys.remote_logins

  exec sp_linkedservers

  exec sp_helpserver

  is there any other SP or View to show linked server including all detail in graphical interface?

  Thanks in advance,

  Sani.

• Jonathan Kehayias

  One Orange Chip

  Points: 26778

  More actions

  December 8, 2009 at 7:29 am

  #1089223

  To figure out how the UI gets the information, start a trace against the server, restrict it to your login name with a filter, and make sure that the SQL:Batch Starting and RPC:Starting Events are in the trace. The in SSMS open the properties sheet for a linked server, and then click on the Security, and Server Options pages. Flip back over to the Profiler window, and you will see the sp_execute calls that SSMS makes to SQL to gather the information. I'm not exactly sure what you are looking for specifically, but you can get the exact query that SSMS uses to build the UI using this method, so it should get you what you need.

  Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
  My Blog | Twitter | MVP Profile
  Training | Consulting | Become a SQLskills Insider
  Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

• josantia

  Ten Centuries

  Points: 1245

  More actions

  December 8, 2009 at 8:24 am

  #1089269

  Hi,

  Some time ago I made a script in Powershell to script all linked servers:

  http://practicascomunes.blogspot.com/2008/08/utilizando-poweshell-con-sql-2005-smo.html

  It's written in spanish, but I think you can translate it with google.

  Hope it helps.

  Regards,

  Jose Santiago Oyervides.