Geek Answers Handbook

SqlConnection ignores user default schema

I apologize for taking so long, but I have seen so many questions that include too little information ... If someone sees my mistake in the first few lines, I will be delighted ...

I have a SQL Server 2008 R2 database and cannot get what, in my opinion, should be correct when connecting through C # SqlConnection.

I have two C # applications created using Visual C # 2010 Express:

  • One to import / export data / reports and view
  • One to perform complex processing

This is all in Windows 7 with all updates, etc.

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

To preserve a certain degree of separation, I created two schemes, two users, two roles and two inputs, for example:

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 several tables and stored procs in each of the schemas corresponding to each role. There can be 20 tables in an engine schema, 30 or so in an import schema. Some of them are very similar in the two schemes, but not quite the same, for example:

 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]

All this works fine when I test the use of these two logins through SSMS - each login sees exactly the tables and SPs that I expect. In each case, I can run queries and USP inside SSMS without having to use a schema prefix, because these connections use the default schema that I configured for each user / user. If I log in as sa, then of course I can see everything in both schemes.

In my C # code, I connect to the database as follows:

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

then I am trying to query the database tables as follows:

 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 using one of my stored procedures, for example:

 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, as in these examples, I get errors such as:

 Invalid object name 'Problem'

or

 Could not find stored procedure 'SelectProblems'

It seems I need to use an explicit schema prefix in order to access the same database objects in the database. Since the schema prefix explicitly included all the things from my C # code, as far as I tested them, change the direct table query to:

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

or try to access the USP with a schema prefix, for example:

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

and then everything works fine.

Now I know that using explicit schema names is best practice, but I have complete code in both C # and stored procedures written without using these schema prefixes. It will be much simpler if I can get C # SqlConnection follow the default schemes for logins that I defined and use. I believe that this should work fine, as I configured it, but I probably must have missed something.

I spent two days on it, and everything I read says that this should all work.

+4
source share
2 answers

Server = laptop; databases = test; user id = Engine_User; password = * ;
Trusted_Connection = yes ; connection timeout = 30

There your problem is - you are using Windows authentication instead of SQL authentication. Change the connection string to Trusted_Connection=no , and everything should work.

+2
source

Set the default schema for users at rmdbs

 USE AdventureWorks; ALTER USER Engine_User WITH DEFAULT_SCHEMA = Engine_Schema; GO

Do the same for the import scheme

0
source

More articles:


All Articles