sys.servers (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

Column nameData typeDescription
server_idintLocal ID of linked server.
namesysnameWhen server_id = 0, this is the server name.

When server_id >0 , this is the local name of linked server.
productsysnameProduct name of the linked server. "SQL Server" indicates this is another instance of SQL Server.
providersysnameOLE DB provider name for connecting to linked server.
data_sourcenvarchar(4000)OLE DB data source connection property.
locationnvarchar(4000)OLE DB location connection property. NULL if none.
provider_stringnvarchar(4000)OLE DB provider-string connection property.

Is NULL unless the caller has the ALTER ANY LINKED SERVER permission.
catalogsysnameOLEDB catalog connection property. NULL if none.
connect_timeoutintConnect time-out in seconds, 0 if none.
query_timeoutintQuery time-out in seconds, 0 if none.
is_linkedbit0 = Is an old-style server added by using sp_addserver, with different RPC and distributed-transaction behavior.

1 = Standard linked server.
is_remote_login_enabledbitRPC option is set enabling incoming remote logins for this server.
is_rpc_out_enabledbitOutgoing (from this server) RPC is enabled.
is_data_access_enabledbitServer is enabled for distributed queries.
is_collation_compatiblebitCollation of remote data is assumed to be compatible with local data if no collation information is available.
uses_remote_collationbitIf 1, use the collation reported by the remote server; otherwise, use the collation specified by the next column.
collation_namesysnameName of collation to use, or NULL if just use local.
lazy_schema_validationbitIf 1, schema validation is not checked at query startup.
is_systembitThis server can be accessed only by the internal system.
is_publisherbitServer is a replication Publisher.
is_subscriberbitServer is a replication Subscriber.
is_distributorbitServer is a replication Distributor.
is_nonsql_subscriberbitServer is a non-SQL Server replication Subscriber.
is_remote_proc_transaction_promotion_enabledbitIf 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).
modify_datedatetimeDate that server information was last changed.

The value in provider_string is always NULL unless the caller has the ALTER ANY LINKED SERVER permission.

Permissions are not required to view the local server (server_id = 0).

When you create a linked or remote server, SQL Server creates a default login mapping to the public server role. This means that by default, all logins can view all linked and remote servers. To restrict visibility to these servers, remove the default login mapping by executing sp_droplinkedsrvlogin and specifying NULL for the locallogin parameter.

If the default login mapping is deleted, only users that have been explicitly added as a linked login or remote login can view the linked or remote servers for which they have a login. To view all linked and remote servers after the default login mapping is deleted requires the following permissions:


  • Membership in the setupadmin or sysadmin fixed server roles

Catalog Views (Transact-SQL)
Linked Servers Catalog Views (Transact-SQL)
sp_addlinkedsrvlogin (Transact-SQL)
sp_addremotelogin (Transact-SQL)

Community Additions