SqlConnection ignores default schema for user

Question

Apologies for this being so long, but I have seen so many questions that include too little info... If someone sees my error(s) in the first few lines, I will be delighted...

I have an SQL Server 2008 R2 database, and can't get what I believe should be the correct behaviour when connecting through a C# SqlConnection.

I have two C# applications built with Visual C# 2010 Express:

• One for data import/export/reporting and browsing
• One for doing some complex processing

This is all on Windows 7 with all updates etc...

Some of the tables used by each of these two applications are shared common tables, and others need to be kept separated. Since I need to be able to transform and transfer data between the two sides, I want to keep all of this in one database.

To keep some degree of separation, I have created two schemas, two users, two roles and two logins, like:

CREATE LOGIN [Import_User] WITH PASSWORD=N'*****'
CREATE LOGIN [Engine_User] WITH PASSWORD=N'*****'

CREATE USER [Import_User] FOR LOGIN [Import_User] WITH DEFAULT_SCHEMA=[Import_Schema]
CREATE USER [Engine_User] FOR LOGIN [Engine_User] WITH DEFAULT_SCHEMA=[Engine_Schema]

CREATE ROLE [Import_Role] AUTHORIZATION [dbo]
CREATE ROLE [Engine_Role] AUTHORIZATION [dbo]

EXEC('CREATE SCHEMA [Import_Schema] AUTHORIZATION [Import_User]')
EXEC('CREATE SCHEMA [Engine_Schema] AUTHORIZATION [Engine_User]')

-- Import role permissions on the Import schema
GRANT EXECUTE, DELETE, INSERT, SELECT, UPDATE, REFERENCES ON SCHEMA::[Import_Schema] TO [Import_Role]

-- Engine_Role permissions on the engine schema
GRANT EXECUTE, DELETE, INSERT, SELECT, UPDATE, REFERENCES ON SCHEMA::[Engine_Schema] TO [Engine_Role]

EXEC sp_addrolemember N'Import_Role',  N'Import_User'
EXEC sp_addrolemember N'Engine_Role',  N'Engine_User'

GRANT CONNECT TO [Import_User]
GRANT CONNECT TO [Engine_User]

Then I create some tables and stored procs in each of the schemas, appropriate to each role. There are maybe 20 tables in the engine schema, 30 or so in the import schema. Some of these are very similar in the two schemas, but not quite the same, like:

CREATE TABLE [Engine_Schema].[Problem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Description] [varchar](max) NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED ( [ID] ASC )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
          ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCK  = ON) ON [PRIMARY]) 
 ON [PRIMARY]

CREATE TABLE [Import_Schema].[Problem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Client] [varchar](50) NOT NULL,
    [Description] [varchar](max) NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED ( [ID] ASC )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
          ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCK  = ON) ON [PRIMARY]) 
 ON [PRIMARY]

This all seems to work fine when I check using those two logins through SSMS - each login sees exactly the tables and SPs that I expect. In each case, I can run queries and USPs inside SSMS without needing to use the schema prefix because these connections use the default schema that I have set up for each login / user. If I login as 'sa', then of course I can see everything in both schemas.

In my C# code, I connect to the database like this:

SqlConnection dbConnection = new SqlConnection(""server=laptop; database=test; user id=Engine_User; password=*****; Trusted_Connection=yes; connection timeout=30");
dbConnection.Open();

then I try to query the database tables directly like this:

using (SqlCommand cmdSelectProblems = new SqlCommand()) {
  cmdSelectProblems.Connection = dbConnection;
  cmdSelectProblems.CommandText = "Select ID, Name from Problem order by Name";
  DataTable dataTableProblems = new DataTable();
  using (SqlDataAdapter dataAdapterProblems = new SqlDataAdapter(cmdSelectProblems)) {
    dataAdapterProblems.Fill(dataTableProblems);
    ...

or I can try to use one of my stored procedures like this:

using (SqlCommand cmd = new SqlCommand()) {
  cmd.Connection = dbConnection;
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "SelectProblems";
  using (SqlDataAdapter da = new SqlDataAdapter(cmd))
    da.Fill(_problemsTable);
}

But when I try to use the connection through my C# code like these examples, I get errors like:

Invalid object name 'Problem'

or

Could not find stored procedure 'SelectProblems'

I seem to need to use the explicit schema prefix to get access to those same DB objects in the database. With the schema prefix explicitly included things all work from my C# code as far as I have tested them, so change the direct table query to:

  cmdSelectProblems.CommandText = "Select ID, Name from [Engine_Schema].Problem order by Name";

or try to access the USPs with the schema prefix like:

  cmd.CommandText = "[Engine_Schema].SelectProblems";

and then everything works fine.

Now I know that using explicit schema names is best practice, but I have a whole shedload of code both in C# and as stored procs that is written without using those schema prefixes. It will be really much simpler if I can make the C# SqlConnection queries respect the default schemas for the logins that I have defined and use. I believe that this should work fine the way I have set it up, but I guess I must have missed something somewhere.

I have wasted two days on this so far, and all that I have read suggests that this should all just work.

Answer 1

server=laptop; database=test; user id=Engine_User; password=*;
Trusted_Connection=yes; connection timeout=30

There's your problem - you're using Windows authentication instead of SQL authentication. Change you connection string to Trusted_Connection=no and everything should work.