dbo schema not showing for tables when tables are created with SQL login

Question

question

Bhaskar asked Apr 30, '10

dbo schema not showing for tables when tables are created with SQL login

I create a table in sql server 2005 database table_1 with sql login "abc" and the table name appears as abc.Table_1, while if I create table on other 2005 server it appears as dbo.Table_1.

Is there any specific reason for this different behaviour.

sql logins at both the server has dbowner right.

sql-server-2005 schema ownership

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-30T12:34:51Z

Kev Riley answered Apr 30, '10

On the first server the default schema for your user 'abc' is 'abc'.

On the second server the default schema for your user is 'dbo', (this is always the case when the user is the database owner).

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Apr 30, 2010 at 12:49 PM

It is considered a dangerous practice not to specify schema when creating tables. If you would do the right thing then there will never be any ambiguity. In other words, the statement creating a table should look like create table [JO_SCHEMA].[JO_TABLE] rather than create table [JO_TABLE] – Oleg 0 secs ago

1 ·

question