SQLServer: Problem choosing a default schema for a user

Question

I named a schema wrong, and all the tables are created with a schema of a wrong name. So I created a one, and use alter schema to transfer the tables over.

First problem is, SQL Server fails when I try to drop the wrong schema, second, although users were created with the new schema as default, I figure I could simply say

select * from table1, 

rather than

select * from myschema.table1, 

but it doesn't work. I have to be explicit. Any ideas what's going on? The weirder thing is, the old user that was created with the wrong schema as default, now is not able to change it. It's kind of frustrating. Please help.

Answer 1

Can you try doing the following :

ALTER USER userName  
WITH DEFAULT_SCHEMA =myschema

Assuming "myschema" is the new schema that you want the user to default to.

I know you mentioned that the new user is created with the new schema but if that is the case, then you should not see this kind of error. So something seems to have gone wrong. Explicitly setting the default schema might correct this.

In general,

1. The default schema of your login session in the current database is the default schema assigned to the current database level principal - database user.

2. If you are referring to an object in the default schema, you do not need to specify the schema name.

3. If you are referring to an object outside the default schema, you must specify the schema name.

For more details regarding the schema for session etc maybe this will also help