when I tried to create a table, it is using my ID as the schema name. But when the table is created on my manager's computer, the schema name is "dbo". Why? He might have administrator rights. When I tried to rename by using sp_rename, it is not changing.


In SSMS go to your database user and click on properties. You will see a field there for default schema.

T-SQL version:

alter user YourDbUser 
with default_schema = YourNewSchema

I wish I could combine the answers of three people here, because the answer really is in three parts, and each part has been answered quite well. So I'll mark this as community wiki.

  1. The reason you are getting a different schema other than dbo is because your default schema is not dbo, and you are writing:

    CREATE TABLE foo(...);

    When you do this, SQL Server assumes you want to create the object in your default schema (part of the purpose of having a custom default schema, after all).

  2. To stop this from happening, always specify the schema prefix when creating or referencing objects. When you write the statement this way, there is no way for SQL Server to get it wrong:

    CREATE TABLE dbo.foo(...);
    CREATE TABLE your_schema.foo(...);
  3. To move a table that was accidentally created in the wrong schema, you can use ALTER SCHEMA. For example, if you created your_schema.foo and you want it in the dbo schema, you can say:

      SELECT 1 FROM sys.tables AS t
        INNER JOIN sys.schemas AS s
        ON t.[schema_id] = s.[schema_id]
        WHERE t.name = N'foo'
        AND s.name = N'dbo'
      ALTER SCHEMA [dbo] TRANSFER your_schema.foo;
      PRINT 'Sorry, couldn''t move the object because it already exists.';

How are you creating the table? By running a SQL script, or in the SQL Server Management Studio GUI?

When you use a SQL script, you can specify the schema in the script:

CREATE TABLE dbo.MyTable ...


CREATE TABLE someschema.MyTable ...

You can find the default schema for a user by looking in the sys.database_principals system view. As to "renaming" it (i.e. moving it to another schema), try:

alter schema [new_schema] transfer [old_schema].[your_object]

Your manager is a member of the sysadmin role. You'll have to specify the actual username in the CREATE TABLE statement in order to get the table to not be owned by dbo.

If you want to change ownership after the table has been created, use sp_changeobjectowner.

  • This is not a side effect of the sysadmin role, it's because the manager has the default schema of dbo. And of course, in SQL Server 2005+, the schema name is not the username or the owner - it's just the schema. – Aaron Bertrand Sep 5 '14 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy