Linked Servers Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.linked_logins
• sys.remote_logins
• sys.servers

sys.linked_logins

Returns a row per linked-server-login mapping, for use by RPC and distributed queries from local server to the corresponding linked server.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
server_id	•	•	•	•	int	ID of the server in sys.servers.
local_principal_id	•	•	•	•	int	Server-principal to whom mapping applies. 0 = wildcard or public.
uses_self_credential	•	•	•	•	bit	If 1, mapping indicates session should use its own credentials; otherwise, 0 indicates that session uses the name and password that are supplied.
remote_name	•	•	•	•	sysname	Remote user name to use when connecting. Password is also stored, but not exposed in catalog view interfaces.
modify_date	•	•	•	•	datetime	Date the linked login was last changed.

TSQL

Sql 2005
SELECT [server_id], [local_principal_id], [uses_self_credential], [remote_name], [modify_date] FROM sys.linked_logins
Sql 2008
SELECT [server_id], [local_principal_id], [uses_self_credential], [remote_name], [modify_date] FROM sys.linked_logins
Sql 2008 R2
SELECT [server_id], [local_principal_id], [uses_self_credential], [remote_name], [modify_date] FROM sys.linked_logins
Sql 2012
SELECT [server_id], [local_principal_id], [uses_self_credential], [remote_name], [modify_date] FROM sys.linked_logins

Back to Top

---

sys.remote_logins

Returns a row per remote-login mapping. This catalog view is used to map incoming local logins that claim to be coming from a corresponding server to an actual local login.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
server_id	•	•	•	•	int	ID of the server in sys.servers. This name is supplied by the connection from the "remote" server.
remote_name	•	•	•	•	sysname	Login name that the connection will supply to be mapped. If NULL, the login name that is specified in the connection is used.
local_principal_id	•	•	•	•	int	ID of the server principal to whom the login is mapped. If 0, the remote login is mapped to the login with the same name.
modify_date	•	•	•	•	datetime	Date the linked login was last changed.

TSQL

Sql 2005
SELECT [server_id], [remote_name], [local_principal_id], [modify_date] FROM sys.remote_logins
Sql 2008
SELECT [server_id], [remote_name], [local_principal_id], [modify_date] FROM sys.remote_logins
Sql 2008 R2
SELECT [server_id], [remote_name], [local_principal_id], [modify_date] FROM sys.remote_logins
Sql 2012
SELECT [server_id], [remote_name], [local_principal_id], [modify_date] FROM sys.remote_logins

Back to Top

---

sys.servers

Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
server_id	•	•	•	•	int	Local ID of linked server.
name	•	•	•	•	sysname	When server_id = 0, this is the instance name. When server_id >0 , this is the local name of linked server.
product	•	•	•	•	sysname	Product name of the linked server. "SQL Server" indicates this is another instance of SQL Server.
provider	•	•	•	•	sysname	OLE DB provider name for connecting to linked server.
data_source	•	•	•	•	nvarchar(4000)	OLE DB data source connection property.
location	•	•	•	•	nvarchar(4000)	OLE DB location connection property. NULL if none.
provider_string	•	•	•	•	nvarchar(4000)	OLE DB provider-string connection property. Is NULL unless the caller has the ALTER ANY LINKED SERVER permission.
catalog	•	•	•	•	sysname	OLEDB catalog connection property. NULL if none.
connect_timeout	•	•	•	•	int	Connect time-out in seconds, 0 if none.
query_timeout	•	•	•	•	int	Query time-out in seconds, 0 if none.
is_linked	•	•	•	•	bit	0 = Is an old-style server added by using sp_addserver, with different RPC and distributed-transaction behavior. 1 = Standard linked server.
is_remote_login_enabled	•	•	•	•	bit	RPC option is set enabling incoming remote logins for this server.
is_rpc_out_enabled	•	•	•	•	bit	Outgoing (from this server) RPC is enabled.
is_data_access_enabled	•	•	•	•	bit	Server is enabled for distributed queries.
is_collation_compatible	•	•	•	•	bit	Collation of remote data is assumed to be compatible with local data if no collation information is available.
uses_remote_collation	•	•	•	•	bit	If 1, use the collation reported by the remote server; otherwise, use the collation specified by the next column.
collation_name	•	•	•	•	sysname	Name of collation to use, or NULL if just use local.
lazy_schema_validation	•	•	•	•	bit	If 1, schema validation is not checked at query startup.
is_system	•	•	•	•	bit	This server can be accessed only by the internal system.
is_publisher	•	•	•	•	bit	Server is a replication Publisher.
is_subscriber	•	•	•	•	bit	Server is a replication Subscriber.
is_distributor	•	•	•	•	bit	Server is a replication Distributor.
is_nonsql_subscriber	•	•	•	•	bit	Server is a non-SQL Server replication Subscriber.
modify_date	•	•	•	•	datetime	Date server information was last changed.
is_remote_proc_transaction_promotion_enabled		•	•	•	bit	If 1, calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC. For more information, see sp_serveroption (Transact-SQL)1.

TSQL

Sql 2005
SELECT [server_id], [name], [product], [provider], [data_source], [location], [provider_string], [catalog], [connect_timeout], [query_timeout], [is_linked], [is_remote_login_enabled], [is_rpc_out_enabled], [is_data_access_enabled], [is_collation_compatible], [uses_remote_collation], [collation_name], [lazy_schema_validation], [is_system], [is_publisher], [is_subscriber], [is_distributor], [is_nonsql_subscriber], [modify_date] FROM sys.servers
Sql 2008
SELECT [server_id], [name], [product], [provider], [data_source], [location], [provider_string], [catalog], [connect_timeout], [query_timeout], [is_linked], [is_remote_login_enabled], [is_rpc_out_enabled], [is_data_access_enabled], [is_collation_compatible], [uses_remote_collation], [collation_name], [lazy_schema_validation], [is_system], [is_publisher], [is_subscriber], [is_distributor], [is_nonsql_subscriber], [is_remote_proc_transaction_promotion_enabled], [modify_date] FROM sys.servers
Sql 2008 R2
SELECT [server_id], [name], [product], [provider], [data_source], [location], [provider_string], [catalog], [connect_timeout], [query_timeout], [is_linked], [is_remote_login_enabled], [is_rpc_out_enabled], [is_data_access_enabled], [is_collation_compatible], [uses_remote_collation], [collation_name], [lazy_schema_validation], [is_system], [is_publisher], [is_subscriber], [is_distributor], [is_nonsql_subscriber], [is_remote_proc_transaction_promotion_enabled], [modify_date] FROM sys.servers
Sql 2012
SELECT [server_id], [name], [product], [provider], [data_source], [location], [provider_string], [catalog], [connect_timeout], [query_timeout], [is_linked], [is_remote_login_enabled], [is_rpc_out_enabled], [is_data_access_enabled], [is_collation_compatible], [uses_remote_collation], [collation_name], [lazy_schema_validation], [is_system], [is_publisher], [is_subscriber], [is_distributor], [is_nonsql_subscriber], [is_remote_proc_transaction_promotion_enabled], [modify_date] FROM sys.servers

Back to Top

---