December 6, 2012

Linked Servers Catalog Views

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

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

No comments:

Post a Comment

Total Pageviews