Why is SQL Server Database User Writing to the Wrong Schema?

Question

In my database, I have 2 schemas: [dbo] and [rch] ([rch] was selected as the name for our "archived" data so that those tables would not appear before the [dbo] tables).

I created a user for this table called rchuser in SQL Server Management Studio (SSMS):

• Notice above that the user is added with rch listed as the Default Schema.

• Notice above that this new user owns both db_owner and rch.

Let's say I have this SQL insert:

public static int AddRecord(object value, string sqlConnection)
{
    int result = 0;
    using (var conn = new System.Data.SqlClient.SqlConnection(sqlConnection))
    {
        conn.Open();
        var sqlCmd =
            "INSERT INTO Table1 ([Value], [Stamp]) " +
            "VALUES (@Value, GETDATE()); ";
        using (var cmd = new System.Data.SqlClient.SqlCommand(sqlCmd, conn))
        {
            cmd.Parameters.AddWithValue("@Value", value);
            result = cmd.ExecuteNonQuery();
        }
    }
    return result;
}

I passed that method a connection string using my rchuser credentials.

 var conn = "Data Source=localhost;Initial Catalog=DatabaseN;User Id=rchuser;Password=password;"

There is no error, result is 1, but the new record is NOT in the corresponding table for my [rch] schema.

It is like the database is completely ignoring the fact that this user defaults to the [rch] schema.

I even tried logging into the SSMS database with rchuser to execute the command by hand. Again, no errors. The data went into [dbo].

How do I direct all database input to go to another schema?

Answer 1

If you have multiple schemas in a database, then my recommendation is to always explicitly specify which one you want. Aaron Bertrand has a good article on why you should do it, even if you only have one schema.

So modify your sql statement to be

INSERT INTO [rch].Table1...

and you will be all good.

I do note that this doesn't answer your title, but it does answer your question.

As to why your query isn't defaulting to the [rch] schema, I don't know. I replicated your setup in SQL Server 2008 R2, and when I run your original query then the inserts do go into the rch table as expected.

EDIT:

I did some Googling, and I suspect that your problem is caused by granting the sysadmin server role to the rchuser login. I modified mine to be an sa

, and now the inserts go into the [dbo] schema by default, even though rchuser has a default schema of [rch].

So, if you remove the sysadmin server role, it should start working as expected.

Answer 2

Have You tried to specifiy schema for table in Your query?

var sqlCmd =
            "INSERT INTO rch.Table1 ([Value], [Stamp]) " +
            "VALUES (@Value, GETDATE()); ";