Sql-server – Change tables from one user to another

With SQL Server 2005 & 2008 the first schema that is searched is the users default schema.

from SQL Server Best Practices – Implementation of Database Object Schemas

Default Schema

Users can be defined with a default schema. The default schema is the first schema that is searched when it resolves the names of objects it references.

and

creating the base tables in the dbo schema will enable table objects to be referred to without an explicit schema name. This is because an object will be located by searching the default schema first, followed by the dbo schema. So:

SELECT * FROM Table1

Will assess the following statement first:

SELECT * FROM <defaultschema>.Table1

If it cannot find the object, the server will assess the following statement:

SELECT * FROM dbo.Table1

The assessment process can be improved by using either the fully qualified name or the DEFAULT_SCHEMA option described earlier. By setting a value for DEFAULT_SCHEMA for the user, the server will check the DEFAULT_SCHEMA first, removing an unnecessary ownership checking process.

This can improve performance considerably on heavily utilized systems.

Default Schema was introduced in SQL Server 2005 Upgrading to SQL Server 2005 and DEFAULT_SCHEMA setting

You can't change the owner of "dbo" because it is a system schema. From ALTER AUTHORIZATION, "Special Cases and Conditions":

SCHEMA
... Cannot change the owner of sys, dbo, or information_schema

And you can't set a default schema for windows logons (yet).

This matters in 2 ways:

• you are issuing DDL (CREATE TABLE) etc when you want objects to be in dbo
• you won't have reusable query plans for DML without schema so different users will have different query plans

Basically, you have two options:

• fix the queries
• stop using ODBCDirect from MS Access to SQL Server.

The third option, ugly and dangerous, is to make the Windows logon "sysadmin" which means they will always map to dbo. I don't recommend this

Personally, I don't use the dbo schema any more: see dba.se (and the further links there)