为什么SQL Server数据库用户写错了架构？

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).

在我的数据库中,我有2个模式:[dbo]和[rch]([rch]被选为我们的“存档”数据的名称,这样这些表就不会出现在[dbo]表之前)。

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

我在SQL Server Management Studio(SSMS)中为此表创建了一个名为rchuser的用户:

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

请注意,用户添加了rch列为默认架构。

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

请注意,此新用户拥有db_owner和rch。

Let's say I have this SQL insert:

假设我有这个SQL插入:

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.

我使用我的rchuser凭据传递了该方法的连接字符串。

 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.

没有错误,结果为1,但新记录不在我的[rch]模式的相应表中。

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

这就像数据库完全忽略了这个用户默认为[rch]模式的事实。

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

我甚至尝试使用rchuser登录SSMS数据库来手动执行命令。再次,没有错误。数据进入[dbo]。

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

2 个解决方案

#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.

如果数据库中有多个模式,那么我的建议是始终明确指定您想要的模式。 Aaron Bertrand有一篇很好的文章说明你为什么要这样做,即使你只有一个模式。

So modify your sql statement to be

所以修改你的sql语句

INSERT INTO [rch].Table1...

INSERT INTO [rch]。表1 ...

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.

至于为什么你的查询不是默认为[rch]模式,我不知道。我在SQL Server 2008 R2中复制了您的设置,当我运行您的原始查询时,插入操作会按预期进入rch表。

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

我做了一些谷歌搜索,我怀疑你的问题是由riguser登录授予sysadmin服务器角色引起的。我修改了我的sa

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

,现在插入默认情况下进入[dbo]模式,即使rchuser的默认模式为[rch]。

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

因此,如果删除sysadmin服务器角色,它应该按预期开始工作。

#2

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

您是否尝试在查询中指定表的架构?

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

#1