A
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
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