Jaa

Identifying a Data Source by Using a Linked Server Name

  • Artikkeli

After a linked server is defined, to reference data objects in that linked server, you can use a four-part name in the form linked_server_name**.catalog.**schema.object_name in Transact-SQL statements. The parts are defined as:

  • linked_server_name
    Linked server that references the OLE DB data source.
  • catalog
    Catalog in the OLE DB data source that contains the object.
  • schema
    Schema in the catalog that contains the object.
  • object_name
    Data object in the schema.

For example, the following query references the Production.Product and Sales.SalesOrderDetails tables in the AdventureWorks database on the linked server SEATTLESales:

SELECT p.Name, sod.SalesOrderID

FROM SEATTLESales.AdventureWorks.Production.Product p

INNER JOIN SEATTLESales.AdventureWorks.Sales.SalesOrderDetail sod

ON p.ProductID = sod.ProductID

ORDER BY p.Name ;

SQL Server uses linked_server_name to identify the OLE DB provider and the data source. The catalog, schema, and object_name parameters are passed to the OLE DB provider to identify a specific data object. When the linked server refers to an instance of SQL Server 2005, catalog refers to a database and schema refers to a schema.

In earlier versions of SQL Server, a schema is the owner of an object. In SQL Server 2005, schemas exist independently of the database user that creates them. For more information about schemas in SQL Server 2005, see User-Schema Separation.

Always use fully qualified names when you are working with objects on linked servers. There is no support for implicit resolution to the dbo owner name for tables in linked servers. Therefore, a query without a schema name generates a 7313 error, even when the linked server is another instance of SQL Server.

The system administrator can control who can access a particular linked server by setting up login mappings by using the sp_addlinkedsrvlogin system stored procedure. This stored procedure enables setting up a remote login and a password for a specific local login. The stored procedure also allows for setting up "self" mappings for Windows authenticated logins.

ms190406.security(en-US,SQL.90).gifSecurity Note:
When connecting to another data source, SQL Server impersonates the login appropriately for Windows authenticated logins; however, SQL Server cannot impersonate SQL Server authenticated logins. Therefore, for SQL Server authenticated logins, SQL Server can access another data source, such as files or nonrelational data sources like Active Directory, by using the security context of the Windows account under which the SQL Server service is running. Doing this can potentially give such logins access to another data source for which they do not have permissions, but the account under which the SQL Server service is running does have permissions. This possibility should be considered when access to a SQL Server authenticated login to a linked server is given by using sp_addlinkedsrvlogin.

See Also

Concepts

Accessing External Data
Distributed Queries

Other Resources

sp_addlinkedsrvlogin (Transact-SQL)
OPENQUERY (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added a syntax example.