Invalid Object Error in spite of the Schema being the default Schema for the User

Question

I've a problem while executing a select query from SSMS (SQL Server 2008).It gives out an error saying that '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 above said error whereas the Query2 works fine and fetches the values. Can someone help me figure the issue here. In spite of having S1 as the default schema for the user 'admin' it still asks me to append the schema name to get the query executed.

Thanks.

Answer 1

I'm going to guess, based on the fact that your user name is "admin", that your user is a member of the sysadmin server role. If that is true, the default schema setting for the user is ignored as all members of the sysadmin role automatically get a default schema of DBO. See the documentation for ALTER USER for further details.