User default schema

29 Oct 2007 3:57 PM

AReel

Hi

Using SQLServer2005, connecting via sql server authentication.
In the Instance I created a database (MyDatabase) and a Login.
In MyDatabase I created a schema (MySchema), Tables (attached to
MySchema) and a User (based on the Login).

I set the Login's default database to MyDatabase.
I set the User as MySchema's owner.

When I connect to the database via SMSE, I can successfully issue SQL
statements without using the MySchema prefix on the Table names.

If I connect to the database via C# and SqlClient (again using sql
server authentication), and issue a query command (without a MySchema
prefix) I get an error stating that my table object does not exist,
(indicating that it is ignoring the default schema for the User). If I
use the prefix the query runs fine.

My mindset is that the default schema used by the connection should be
that stated in SqlServer for the given user (in this case MySchema).
However the observed behaviour is suggesting that I need to do more in
order to ensure that MySchema is used as the User's default schema.

So two questions:

Am I understanding of this problem correct?

How can I ensure that when I connect via ADO.NET that the connection
will use the default schema attached to the user?

Regards

Aidan

31 Oct 2007 4:10 PM

Mary Chipman [MSFT]

Think of a schema as simply a container for objects. A user's default
schema could be "Sales" but they also need to access tables in the
"HR" schema. The bottom line is, you *always* need to use the
schema.object naming syntax when connecting from ADO.NET. User-schema
separation is described in SQL BOL - see
http://msdn2.microsoft.com/en-us/library/ms190387.aspx.

-Mary

On Mon, 29 Oct 2007 15:57:41 +0000, AReel <areel@spamName.com> wrote:

Show quote

>Hi
>
>Using SQLServer2005, connecting via sql server authentication.
>In the Instance I created a database (MyDatabase) and a Login.
>In MyDatabase I created a schema (MySchema), Tables (attached to
>MySchema) and a User (based on the Login).
>
>I set the Login's default database to MyDatabase.
>I set the User as MySchema's owner.
>
>When I connect to the database via SMSE, I can successfully issue SQL
>statements without using the MySchema prefix on the Table names.
>
>If I connect to the database via C# and SqlClient (again using sql
>server authentication), and issue a query command (without a MySchema
>prefix) I get an error stating that my table object does not exist,
>(indicating that it is ignoring the default schema for the User). If I
>use the prefix the query runs fine.
>
>My mindset is that the default schema used by the connection should be
>that stated in SqlServer for the given user (in this case MySchema).
>However the observed behaviour is suggesting that I need to do more in
>order to ensure that MySchema is used as the User's default schema.
>
>So two questions:
>
>Am I understanding of this problem correct?
>
>How can I ensure that when I connect via ADO.NET that the connection
>will use the default schema attached to the user?
>
>
>Regards
>
>Aidan