Hello and welcome! I recently received a request to retrieve linked server details from a SQL server. DBAs were performing a migration activity, and clearly linked servers had to be moved to the new version of SQL server as well.
The first step in any migration is to collect data from your old server and discuss it with the appropriate parties.
Going to SSMS and noting down each and every linked server with its properties is a time-consuming task.
This is when SQL queries come in handy.
I have given them below query which can be very helpful to you.
SELECT @@servername as [Server Name]
,ss.server_id
,ss.name
,'Server' = case ss.server_id
when 0 then 'Current Server'
else 'Remote Server'
end
,ss.product
,ss.provider
,ss.catalog
,'Local Login' = case sl.uses_self_credential
when 1 then 'Uses Self Credential'
else ssp.name
end
,'Remote Login Name' = sl.remote_name
,'RPC Out Enabled' = case ss.is_rpc_out_enabled
when 1 then 'True'
else 'False'
end
,'Data Access Enabled' = case ss.is_data_access_enabled
when 1 then 'True'
else 'False'
end
,ss.modify_date
FROM sys.servers ss
LEFT JOIN sys.linked_logins sl
ON ss.server_id = sl.server_id
LEFT JOIN sys.server_principals ssp
ON ssp.principal_id = sl.local_principal_id
Below is the example of output of this command.
Let me know in comments if it is helpful to you.
Happy Learning!!
SQL Automation Guru 