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
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?