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.

more ▼

asked Apr 30, 2010 at 12:28 PM in Default

Bhaskar gravatar image

333 16 17 20

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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

more ▼

answered Apr 30, 2010 at 12:34 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

thanks, I checked both the servers and they both have default schema 'abc'. It is really confusing, I missing something.
Apr 30, 2010 at 12:43 PM Bhaskar
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
Apr 30, 2010 at 12:49 PM Oleg
on the 2nd server is the user the database owner?
Apr 30, 2010 at 12:49 PM Kev Riley ♦♦
Are you sure that the abc login is not sa on one of the servers but not the other. sa logins come in as dbo, this can make a difference.
Apr 30, 2010 at 12:51 PM Oleg
That's the only difference you need to find....
Apr 30, 2010 at 01:10 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 30, 2010 at 12:28 PM

Seen: 2879 times

Last Updated: May 02, 2010 at 05:06 AM