What determines the default schema name in SQL Server?

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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 ...

or

CREATE TABLE someschema.MyTable ...

Answer 4

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]