Object Visibility and Qualification Rules

When you create an object, Microsoft SQL Server uses the following defaults for the parts of the name not specified.

For example, if a user is logged on to AdventureWorks2008R2 as the database owner user, dbo, either of the following two statements creates a table that is named AdventureWorks2008R2.dbo.TableX:

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

Or

CREATE TABLE AdventureWorks2008R2.dbo.TableX
      (cola INT PRIMARY KEY, colb NCHAR(3));
GO

Similarly, when you refer to an object, SQL Server uses the following defaults for the parts of the name not specified .

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 as follows:

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 following example, LoginX executes a SELECT statement on a table that is not in the current database:

USE DBY;
GO
SELECT * FROM DBZ..TableY;
GO

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.

The visibility for stored procedures that start with sp_ differs from the visibility for regular stored procedures. For more information, see CREATE PROCEDURE (Transact-SQL).