Add link server
EXEC sp_addlinkedserver @server=N'LINK_SERVER_NAME', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'REMOTE_SERVER_CONECTION_STRING'; EXEC sp_addlinkedsrvlogin 'LINK_SERVER_NAME', 'false', NULL, 'REMOTE_USER_LOGIN', 'REMOTE_USER_PASSWORD';
Remove link server
EXEC sp_dropserver 'LINK_SERVER_NAME' [,'droplogins']
Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the network name of the server, the replication status of the server, the identification number of the server, and the collation name.
EXEC sp_helpserver
This script will get the list of Linked server with its properties and its associated local/Remote login list.
SELECT
ss.server_id
, ss.name
, ss.data_source
,'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 Credentials'
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