Invalid object error, even though the schema is the default schema for the user

Question

I had a problem executing a select query from SSMS (SQL Server 2008). It gives an error "Invalid object"

User Name: admin DefaultSchema: S1 Table being accessed: employee 

Query1:

 select * from employee 

Query2:

 select * from S1.employee 

In this case, Query1 fails with the above error, while Query2 works fine and retrieves the values. Can someone help me figure it out here. Despite having S1 as the default schema for the user “admin”, he still asks me to add a schema name to fulfill the request.

Thanks.

Answer 1

I'm going to guess from the assumption that your username is “admin” that your user is a member of the sysadmin server role. If so, the default schema setting for the user is ignored, since all members of the sysadmin role automatically receive the standard DBO schema. See the documentation for ALTER USER for more information.