sys.servers (Transact-SQL)

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

Column name Data 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.

Permissions

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:

  • ALTER ANY LINKED SERVER or ALTER ANY LOGIN ON SERVER
  • Membership in the setupadmin or sysadmin fixed server roles

See Also

Reference

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

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006

Updated content:
  • Added content about linked and remote server visibility in the Permissions section.

14 April 2006

Updated content:
  • Corrected the permissions required to view the provider_string column.