Default Schema allows dbo table selection without using schema prefix

Question

I have created a Login and a database user on my server as follows:

USE master
GO
CREATE LOGIN MyLogin WITH PASSWORD=N'Password123!'
GO
USE AdventureWorks2014
GO
CREATE USER MyLogin FOR LOGIN MyLogin
GO
ALTER USER MyLogin WITH DEFAULT_SCHEMA=HumanResources
GO
GRANT SELECT ON SCHEMA :: Production TO MyLogin
GO
GRANT SELECT ON SCHEMA :: HumanResources TO MyLogin
GO
GRANT SELECT ON SCHEMA :: dbo TO MyLogin

When I log in as MyLogin I can now select objects in the HumanResources schema without using the four part name (as HumanResources is the default schema for this user)

SELECT * FROM HumanResources.Department

can now be run without the schema name:

SELECT * FROM Department

This is fine. If I then try to select from a table in the Production Schema without using the schema prefix:

SELECT * FROM ProductDescription

I get an error as expected. This can be remedied by using:

SELECT * FROM Production.ProductDescription

So based on this, I need to specify the schema name when selecting from any table outside the default schema.

So why when I select from a table in the dbo schema, do I still not need to use the schema prefix?

SELECT * FROM DatabaseLog

returns results

I am confused. I thought if a schema name does not prefix the table in a SELECT statement, that the default schema is used and selecting from a table in any other schema would require the schema prefix.

Is the dbo schema an exception to this rule?

Answer 1

You don't need the prefix when some object is in your default schema because SQL Server checks for a non-schema-prefixed object in your default schema first. It will then check the dbo schema, so you can actually get away with this laziness in two different ways.

From the documentation:

When database objects are referenced by using a one-part name, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema. If the object is not in the dbo schema, an error is returned.

They say "one-part name" but this also applies when you reference an object with a pattern like:

[server].[database]..[name]

or

[database]..[name]

This is nothing but a confirmation that you should just always specify schema name. Always.

• Bad habits to kick : avoiding the schema prefix