How to change the owner of a SQL Server table from domain\username to dbo?

Question

When I create a table, the owner is always domain\username instead of dbo. For example, domain\username.tablename I know how how to change it to dbo but what should I change in the configuration level to make sure that when I create a table, the default owner is dbo. like dbo.tablename ?

Thanks

When you create the table append the dbo in front of it like this: Create Table.. dbo.TableName — Brad, Nov 16 '18 at 18:14
If you specify the schema (which you really should be when referencing objects) this problem disappears. — Thom A, Nov 16 '18 at 18:40

John Cappelletti · Answer 1 · 2018-11-16T18:26:13.607

3

You can change the DEFAULT SCHEMA via the Object Explorer

Security > Users > {Your User}

{right-click} > Properties

Then on the General Tab set Default schema

edited Nov 16 '18 at 18:26

answered Nov 16 '18 at 18:17

John Cappelletti

79,615
7
44
66

Hi John, I tried that but the dbo remains the default. Do i need to start the SQL server ? Thanks – Joie Tamayo Nov 16 '18 at 18:27
@JoieTamayo Should not be necessary – John Cappelletti Nov 16 '18 at 18:45
@JoieTamayo It dawned on me that may be expecting already created tables to be renamed to dbo. Do as above, any new table would be dbo. Then to change existing take a peek at https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-2016 – John Cappelletti Nov 16 '18 at 19:32
Yes, but I would like to change the default. Thanks – Joie Tamayo Nov 16 '18 at 19:45

score 2 · Answer 2 · answered Nov 16 '18 at 18:18

2

This can also be done using the T-SQL statement below:

ALTER USER [Domain\UserName] WITH DEFAULT_SCHEMA=[dbo]

answered Nov 16 '18 at 18:18

Hamed Naeemaei

8,052
3
37
46

score 1 · Answer 3 · answered Nov 16 '18 at 18:45

1

Follow this steps:

In the Object Explorer, expand your database node, go to "Security" folder and expend that node too.
You will see "Users" folder, expend that node too to see the users.
Right click on your user
Click propertise from the menu appears.
In general tab, you will see "Default Schema", click on the button "..." on the right
Select "dbo" schema and click "OK" button.

Now, try to create a new table.

answered Nov 16 '18 at 18:45

Ilyes

14,640
4
29
55

1

Hi Sami, that's what i did and it's always dbo. Thanks – Joie Tamayo Nov 16 '18 at 19:42

How to change the owner of a SQL Server table from domain\username to dbo?

3 Answers3