December 8, 2009 at 5:38 am
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.
December 8, 2009 at 7:29 am
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]
December 8, 2009 at 8:24 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply