sql - 使用单数据库多架构的 ASP.NET MVC Multi-Tenancy 应用程序 : Calling Store Procedures from the application tries to access dbo tables

Question

我正在使用 ASP.NET MVC、SQL Server、Dapper 开发一个 Multi-Tenancy 应用程序，每个租户具有单个数据库和多个架构。租户将被分配一个拥有租户架构的数据库用户。我在 dbo 中有一组表，另一组由所有架构共享。

假设我有 dbo.Tenant 和 anySchema.Table1。在为anyschema用户设置连接字符串的应用程序中，如果我调用select * from Table1，它将返回anySchema.Table1中的值。如果我在存储过程中有相同的查询，它会在尝试访问 dbo.Table1 时抛出错误。

我已为租户数据库用户提供了对 dbo 的执行访问权限，因为位于 dbo.SP_Name 的所有租户共享单个 SP

我如何执行以租户数据库用户身份登录的存储过程，访问anyschema的Table1，以便所有租户都可以使用单个SP。

Answer 1

假设我们的数据库中有两个用户 tenant1 和 tenant2 拥有自己的架构(也称为 tenant1 和 tenant2).

我们为每个租户创建一个Contacts表，因此我们有tenant1.Contacts和tenant2.Contacts。

在我们的应用程序中，我们使用 Dapper 执行以下 SQL。我们正在使用租户特定的连接字符串，该连接字符串使用租户的 SQL Server 登录详细信息。

select * from Contacts

由于我们没有在 SQL 中指定架构，因此是从执行 SQL 的用户的默认架构中推断出来的。因此，对于 tenant1 它将查询 tenant1.Contacts，对于 tenant2 它将查询 tenant2.Contacts。

现在我们将在执行相同查询的 dbo 架构中创建一个存储过程:

CREATE PROCEDURE GetContacts
AS
BEGIN
    select * from Contacts
END
GO

请注意，我没有在存储过程的 SQL 中指定架构。

当我们从应用程序执行存储过程时，存储过程的架构仍然可以从当前用户推断但是 SP 中的 SQL 从 SP 的架构推断架构。因此，如果 SP 属于 dbo 架构，它将针对 dbo.Contacts 表执行查询。

这就是您的情况所发生的情况。您已在 dbo 架构中创建了一个存储过程，并期望它针对租户的默认架构执行 SQL。

你有几个选择

1. 将架构作为参数传递给存储过程并使用动态 SQL - How to pass schema as parameter to a stored procedure in sql server?
2. 为每个租户创建一个 SP 版本，例如tenant1.SP_Name、tenant2.SP_Name
3. 不要使用存储过程。