Contents
How to query a database without specifying a schema?
An alternative if you like having the non-dbo schema is to set your user’s default schema to erpadmin then if you do not specify a schema, it will use that as default. (Members of the sysadmin fixed server role ignore the DEFAULT_SCHEMA and use dbo by default.)
Why does SQL Server pick the wrong schema?
This can cause problems, obviously, if you have a object called your_default_schema.foo and there is also an object in the database called dbo.foo. SQL Server will pick the one under your default schema. This might be the wrong choice, but how can it know what you intended?
Where do I find the default schema in SQL Server?
All members of the sysadmin fixed server role have a default schema of dbo. Is your user listed under Security > Users (in SSMS)? Check the Properties (right click the name), and see if the Default schema is set in the context of the database, rather than the instance (which is what ALTER USER is setting).
How to go back to DBO schema in SQL Server?
If you want to go back to using the dbo schema like you were in SQL Server 2000, you can move the table back into the dbo schema: An alternative if you like having the non-dbo schema is to set your user’s default schema to erpadmin then if you do not specify a schema, it will use that as default.
Is the schema in SQL Server the same as the user?
If you are using sql server 2000 or older this page explains that users and schemas are then equivalent. If you don’t prepend your table name with a schema\ser, sql server will first look at the tables owned by the current user and then the ones owned by the dbo to resolve the table name.
Why do you need to change default schema on the user?
If you do not want to use “full qualified” SQl names, then you need to avoid creating your tables using any account or role that’s not using the “dbo” default schema assigned. Why do you need to change the default schema on the user if you don’t plan on using it?