| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.servers (Transact-SQL) |
sys.servers (Transact-SQL) |
rwestMSFT |
randolphwest |
06/16/2020 |
sql |
system-objects |
reference |
|
|
|
=azuresqldb-mi-current||>=sql-server-2016||>=sql-server-linux-2017 |
[!INCLUDE SQL Server - ASDBMI]
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, the returned value is the server name. When server_id > 0, the returned value is the local name of linked server. |
| product | sysname | Product name of the linked server. A value of "SQL Server" indicates another instance of [!INCLUDEssNoVersion]. |
| provider | sysname | OLE DB provider name for connecting to linked server. Starting with [!INCLUDEsql-server-2019], the value "SQLNCLI" maps to the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) by default. In earlier versions, the value "SQLNCLI" maps to the SQL Server Native Client OLE DB provider (SQLNCLI11). |
| 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 | OLE DB 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. |
| 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). |
| modify_date | datetime | Date that server information was last changed. |
| is_rda_server | bit | Applies to: Starting with [!INCLUDEsssql16-md]. Server is remote data archive enable (stretch-enabled). For more information, see Enable Stretch Database on the server. |
[!INCLUDEsnac-removed-oledb-and-odbc]
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, [!INCLUDEssNoVersion] creates a default login mapping to the public server role. Default login mapping means that 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. The following permissions are required to view all linked and remote servers after the default login mapping:
ALTER ANY LINKED SERVERorALTER ANY LOGIN ON SERVER- 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)