SQL Server creating wrong table names, why?

Question

Question: when I create a table (T_TableName) using SQL Server Management-Studio, it always creates the table as

Domain\UserName.T_TableName

instead of

dbo.T_TableName

What's wrong ?

Answer 1

If you don't specify a schema explicitly on your table name to be created, it will be created in the user's current default schema.

I bet the user you're using has its own personal schema set as its default schema - that's why your tables get created in his own personal schema.

You can check what database users you have and what their default schema is by inspecting sys.database_principals (SQL Server 2005 and up):

SELECT name, type_desc, default_schema_name
FROM sys.database_principals

To solve this:

• specify the schema you want to use explicitly (best practice anyway!)

  CREATE TABLE dbo.T_TableName
  

• change the user's default schema to dbo

  ALTER USER [Domain\YourUser] WITH DEFAULT_SCHEMA = dbo
  

But as a general rule of thumb, I recommend always using the "dbo." prefix explicitly, if you want to have all your database objects in the dbo schema. Helps with performance, too (ever so slightly) since SQL Server won't have to go hunting in different schemas, if you explicitly tell it where your db objects live.