[RESOLVED] I need help with default schema

[MrNorth]

Hi!

A user is complaining about a database I have set up. The setup is like this:

I created a database A

I created a login Donald
I created a user Donald

The user Donald has Donald as the default schema. So in database A all tables looks like this

Donald.TableA
Donald.TableB
...

The login Donald is connected to the database A, user Donald and default schema Donald.

The problem is that when the user log on , he must write

select * from Donald.TableA Where.....

He wants to write select * from TableA..


I am rather new to SQL Server 2005, what have I missed here? I am using Management Studio..

The connection stirng looks like this:
Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=A;User ID=Donald;Password=pass;Default Schema=Donald"

kind regards
Henrik

[wild_bill]

Have they tried "select * from tableA" ? It works for me on our databases. But I did read some where that qualifying the table with the schema is better for performance?

[MrNorth]

Hi!

Yes that is the question he wants to run. But then he get a message stating that TableA doesn not exist, unknown object. But teh same questy when using Donald. in front of the table name works perfect.


what have I missed?

/HEnrik

[GaryMazzone]

I think SQL Server always checks dbo for tables, other then that you need to put schema in

[MrNorth]

Hi!

Yes I think there is something with the dbo... any suggestion on how I should change it?

kind regards
Henrik

[GaryMazzone]

I always put the schema name if the object is not in the dbo schema.

[MrNorth]

Ok!

I solved the problem by manually change the ownership of the tables from Donald to dbo, now it works, thanks!

/Henrik