Disable dbo schema fallback when table doesn't exist in default schema I've properly performed these SQL commands in SQL server 2017:

CREATE DATABASE DataTeamDatabase

USE DataTeamDatabase

CREATE LOGIN DataTeamUser
    WITH PASSWORD = 'YourStrong!Passw0rd',
    DEFAULT_DATABASE = DataTeamDatabase;

CREATE SCHEMA DataTeamSchema

CREATE USER DataTeamUser FOR LOGIN DataTeamUser WITH DEFAULT_SCHEMA = DataTeamSchema

ALTER ROLE db_datareader add member DataTeamUser
ALTER ROLE db_datawriter add member DataTeamUser
ALTER ROLE db_ddladmin add member DataTeamUser
GRANT CONNECT TO datateamuser

CREATE TABLE dbo.TestTable (column_1 int)

ALTER USER DataTeamUser WITH DEFAULT_SCHEMA = DataTeamSchema;


Now, let's say that I want to query the TestTable (which I know is not existing in DataTeamSchema):

SELECT * FROM TestTable


Since the table TestTable is not existing in the DataTeamSchema, SQL server is looking it up in the dbo schema and uses it.

Is there a way to disable this behavior? I know that prepending the schema name to the table name will work, but I would like to avoid that because of different reasons.

I searched through the SQL server documentation but didn't find anything so far.

Thanks, 

Mattia
In a word, no this is not possible.

I've struggled to find an official line on this, but the name resolution algorithm in SQL Server is something like the following, as defined by Bob Beauchemin in Dude, Where's my table?

If batch or dynamic SQL:


Look in 'sys' schema for system objects
Look in user's default schema
Look in dbo schema


If procedural code: 


Look in 'sys' schema for system objects
Look in procedure schema
Look in dbo schema


In your case, SQL Server is failing to find an MS Shipped object firstly, then failing to find the object in the user schema (DataTeamSchema), and finally it finds the object in the dbo schema.

One way around this would be to change the original object to a schema other than dbo, as the dbo schema is a special case in terms of object-name resolution.

There does not appear to be a documented way to avoid this behaviour.