Object Visibility and Qualification Rules

When you create an object, Microsoft® SQL Server™ uses the following defaults for the parts of the name that are not specified:

• server defaults to the local server.
• database defaults to the current database.
• owner_name defaults to the username in the specified database associated with the login ID of the current connection.

When you create an object, SQL Server uses the defaults to build the fully qualified name. For example, if a user is logged into Northwind as the database owner (dbo) user, either of the following two statements creates a table named Northwind.dbo.TableX:

CREATE TABLE TableX (cola INT PRIMARY KEY, colb NCHAR(3))

or

CREATE TABLE Northwind.dbo.TableX

        (cola INT PRIMARY KEY, colb NCHAR(3))

Note It is recommended that the full table or view name be specified to eliminate possible confusion relating to the object in question.

When you refer to an object, Microsoft® SQL Server™ uses the following defaults for the parts of the name that are not specified:

• server defaults to the local server.
• database defaults to the current database.
• owner_name defaults to the username in the specified database associated with the login ID of the current connection. If that user owns no object with the specified name, SQL Server looks for an object with the specified name that is owned by the database owner (dbo) user.

For example, assume LoginX connects to a server that has two databases: DBY and DBZ. LoginX is associated with UserA in database DBY and with UserB in database DBZ.

LoginX executes a SELECT statement in the current database.

USE DBY

SELECT * FROM DBY..TableX

Because LoginX is associated with UserA in DBY, SQL Server first looks for DBY.UserA.TableX. If there is no table with this name, SQL Server looks for a table DBY.dbo.TableX.

In the next example, LoginX executes a SELECT statement on a table that is not in the current database:

USE DBY

SELECT * FROM DBZ..TableY

Because LoginX is associated with UserB in database DBZ, SQL Server first looks for DBZ.UserB.TableY. If there is no table with this name, SQL Server then looks for a table DBZ.dbo.TableY.

Note SQL Server does not try to deduce the owner of remote tables based on the current login. To ensure that distributed queries execute properly, use fully-qualified names.

The visibility for stored procedures that begin with sp_ differs from the visibility for regular stored procedures. For more information, see CREATE PROCEDURE.