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

Call it dbo.T_TableName in SSMS. If you have the correct permissions, it will work.

Answer 2

Are you assigned as db_owner for the database you created the table in? If not, this could be the issue. Try adding your user mapping permissions to the database as such.

USE [yourDatabase]
GO
EXEC sp_addrolemember N'db_owner', N'DOMAIN\UserOrGroup'
GO

Answer 3

You need to either create your table as "dbo.Whatever", OR you need to change your default schema (or have your SA do it for you) by issuing a command like:

ALTER USER [DOMAINNAME\UserName] WITH DEFAULT_SCHEMA = dbo;

Answer 4

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.