Default schema is ignored on SELECT

Question

According to my understanding, a default schema should apply when issuing SQL statements on tables using unqualified table names.

My problem is that the following statement fails, because the table name cannot be resolved.

-- Table name : [mydb].[myschema].[mytable]
USE mydb
SELECT * FROM mytable

Msg 208, Level 16, State 1, Line 2
Invalid object name 'mytable'.

I have a database user defined based on a Windows login user mydomain\myuser with the following properties:

• The login user is not a member of the sysadmin group, but only of serveradmin and public. According to the documentation, these memberships should not be a problem.
• The mapped database user (also called mydomain\myuser) has the default schema myschema assigned.
• The database user is member of db_owner.

When connecting with MS SQL Management Studio as mydomain\myuser, calling SCHEMA_NAME() returns dbo. This indicates that for some reason, the default schema myschema is ignored. This is consistent with the failed SELECT statement.

But why is the default schema ignored?

Answer 1

This article gave me the hint:

It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user.

My user myuser was a member of the Windows Administrators group and there was an SQL Server login defined for the Administrators group.

After removing the user from this group, everything works fine.

I've read this documentation before, but skipped the crucial paragraph.