Object Name Resolution – SQL Server

Understanding how SQL Server resolves objects’ names will be an effective remedy against performance issues. This name resolution happens if securables are not fully qualified. A statement like SELECT SalesOrderID FROM SalesOrderHeader will be a candidate for this. As you see, the SalesOrderHeader is not written as a fully qualified name, hence SQL Server tries to resolve it by adding Schema owned by user connected. If SQL Server cannot find an object like ConnectedUser’sSchema.SalesOrderHeader then it tries to find an object called dbo.SalesOrderHeader as the second step. Error is thrown, if it is not successful too. Here is an example for it;

USE AdventureWorks2008

GO

-- creating a new table with dbo schema

SELECT TOP (1) * 

INTO SalesOrderHeader

FROM Sales.SalesOrderHeader

-- Create two logins for testing

CREATE LOGIN HR_Manager WITH PASSWORD = '123'

    , CHECK_POLICY = OFF;

CREATE LOGIN Sales_Manager WITH PASSWORD = '123'

    , CHECK_POLICY = OFF;

GO

-- Adding them as users with default schemas

CREATE USER HR_Manager WITH DEFAULT_SCHEMA = HumanResources

CREATE USER Sales_Manager WITH DEFAULT_SCHEMA = Sales

GO

-- Given users permission on data reading

sp_addrolemember 'db_datareader', 'HR_Manager';

sp_addrolemember 'db_datareader', 'Sales_Manager';

-- Execute as HT_Manager and see

EXECUTE AS USER = 'HR_Manager'

GO

-- This shows records from newly created

-- table, not from original table

SELECT * FROM SalesOrderHeader

-- This throws an error

SELECT * FROM SalesOrderDetail

REVERT

GO

-- Execute as Sales_Manager

EXECUTE AS USER = 'Sales_Manager'

GO

-- Both statements will work

SELECT * FROM SalesOrderHeader

SELECT * FROM SalesOrderDetail

REVERT

GO

As you see with the code, when HR_Manager executes SELECT statements, as the first step, names of them will be resolved as HumanResources.SalesOrderHeader and HumanResources.SalesOrderDetail. SQL Server does not find any objects and do the next step, resulting dbo.SalesOrderHeader and dbo.SalesOrderDetail. A match will be found for first one as we have created one with dbo schema but not for the second. In that case, second statement throws an error.

When Sales_Manager executes, SQL Server resolves objects’ names as Sales.SalesOrderHeader and Sales.SalesOrderDetail. In that case, both statements are successfully executed.

Even though Sales_Manager executes statements without any error, it would be always better to make securables as fully qualified objects, that helps SQL Server to quickly execute the code without performing an additional task.