Identifying a Data Source by Using a Linked Server Name
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 that references the OLE DB data source.
Catalog in the OLE DB data source that contains the object.
Schema in the catalog that contains the object.
Data object in the schema.
For example, the following query references the
Sales.SalesOrderDetails tables in the
AdventureWorks database on the linked server
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.
|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.|