I am developing a multi tenant application using ASP.NET MVC, SQL Server, Dapper with single db and multiple schema for each tenant. A tenant will be assigned a db user who owns the tenant schema. I have a set of tables in dbo and another set shared by all schema.
我正在使用ASP.NET MVC，SQL Server，Dapper开发一个多租户应用程序，每个租户使用单个数据库和多个模式。将为租户分配拥有租户架构的db用户。我在dbo中有一组表，而另一组在所有模式*享。
Say I have dbo.Tenant and anySchema.Table1. From my application with connection string set for anyschema user, if i call select * from Table1 it returns the values from anySchema.Table1. If i have the same query in a store procedure, it throws an error as it tries to access dbo.Table1.
说我有dbo.Tenant和anySchema.Table1。从我为anyschema用户设置连接字符串的应用程序，如果我从Table1调用select *，它将返回anySchema.Table1中的值。如果我在存储过程中有相同的查询，它会在尝试访问dbo.Table1时抛出错误。
I have provided execute access to dbo for the tenant db user as single SP is being shared by all tenant which is at dbo.SP_Name
我为租户db用户提供了对dbo的执行访问权限，因为所有租户都在dbo.SP_Name共享单个SP
How can i execute the store procedure logged in as the tenant db user, accessing Table1 of the anyschema so that single SP can be used by all tenants.
如何执行以租户db用户身份登录的存储过程，访问anyschema的Table1，以便所有租户都可以使用单个SP。
1 个解决方案
#1
2
Assume we have two users in our database tenant1 and tenant2 with their own schemas (also called tenant1 and tenant2).
假设我们的数据库tenant1和tenant2中有两个用户拥有自己的模式（也称为tenant1和tenant2）。
We create a Contacts table for each tenant so we have tenant1.Contacts and tenant2.Contacts.
我们为每个租户创建一个Contacts表，因此我们有tenant1.Contacts和tenant2.Contacts。
Within our application we execute the following SQL using Dapper. We are using a tenant specific connection string that uses the tenant's SQL Server login details.
在我们的应用程序中，我们使用Dapper执行以下SQL。我们使用租户特定的连接字符串，该字符串使用租户的SQL Server登录详细信息。
select * from Contacts
Since we did not specify the schema within the SQL it is inferred from the default schema of the user under which the SQL executed. So for tenant1 it will query tenant1.Contacts and for tenant2 it will query tenant2.Contacts.
由于我们没有在SQL中指定模式，因此它是从SQL执行的用户的默认模式中推断出来的。因此对于tenant1，它将查询tenant1.Contacts，对于tenant2，它将查询tenant2.Contacts。
Now we'll create a stored procedure in the dbo schema that executes the same query:
现在我们将在dbo架构中创建一个执行相同查询的存储过程：
CREATE PROCEDURE GetContacts
AS
BEGIN
select * from Contacts
END
GO
Note that I've not specified a schema in the Stored Procedure's SQL.
请注意，我没有在存储过程的SQL中指定模式。
When we execute the stored procedure from our application the schema of the stored procedure can still be inferred from the current user but the SQL within the SP infers the schema from that of the SP. So if the SP belongs to the dbo schema it will execute the query against the dbo.Contacts table.
当我们从应用程序执行存储过程时，仍然可以从当前用户推断存储过程的模式，但SP中的SQL推断出来自SP的模式。因此，如果SP属于dbo架构，它将对dbo.Contacts表执行查询。
This is what is happening in your situation. You have created a stored procedure in the dbo schema and expecting it to execute the SQL against the tenant's default schema.
这就是你的情况。您已在dbo架构中创建了一个存储过程，并期望它根据租户的默认架构执行SQL。
You have a few options
你有几个选择
- Pass the schema to your stored procedure as a parameter and use dynamic SQL - How to pass schema as parameter to a stored procedure in sql server?
- 将模式作为参数传递给存储过程并使用动态SQL - 如何将模式作为参数传递给sql server中的存储过程？
- Create a version of the SP for each tenant e.g.
tenant1.SP_Name,tenant2.SP_Name - 为每个租户创建一个SP版本，例如tenant1.SP_Name，tenant2.SP_Name
- Don't use stored procedures.
- 不要使用存储过程。
#1
2
Assume we have two users in our database tenant1 and tenant2 with their own schemas (also called tenant1 and tenant2).
假设我们的数据库tenant1和tenant2中有两个用户拥有自己的模式（也称为tenant1和tenant2）。
We create a Contacts table for each tenant so we have tenant1.Contacts and tenant2.Contacts.
我们为每个租户创建一个Contacts表，因此我们有tenant1.Contacts和tenant2.Contacts。
Within our application we execute the following SQL using Dapper. We are using a tenant specific connection string that uses the tenant's SQL Server login details.
在我们的应用程序中，我们使用Dapper执行以下SQL。我们使用租户特定的连接字符串，该字符串使用租户的SQL Server登录详细信息。
select * from Contacts
Since we did not specify the schema within the SQL it is inferred from the default schema of the user under which the SQL executed. So for tenant1 it will query tenant1.Contacts and for tenant2 it will query tenant2.Contacts.
由于我们没有在SQL中指定模式，因此它是从SQL执行的用户的默认模式中推断出来的。因此对于tenant1，它将查询tenant1.Contacts，对于tenant2，它将查询tenant2.Contacts。
Now we'll create a stored procedure in the dbo schema that executes the same query:
现在我们将在dbo架构中创建一个执行相同查询的存储过程：
CREATE PROCEDURE GetContacts
AS
BEGIN
select * from Contacts
END
GO
Note that I've not specified a schema in the Stored Procedure's SQL.
请注意，我没有在存储过程的SQL中指定模式。
When we execute the stored procedure from our application the schema of the stored procedure can still be inferred from the current user but the SQL within the SP infers the schema from that of the SP. So if the SP belongs to the dbo schema it will execute the query against the dbo.Contacts table.
当我们从应用程序执行存储过程时，仍然可以从当前用户推断存储过程的模式，但SP中的SQL推断出来自SP的模式。因此，如果SP属于dbo架构，它将对dbo.Contacts表执行查询。
This is what is happening in your situation. You have created a stored procedure in the dbo schema and expecting it to execute the SQL against the tenant's default schema.
这就是你的情况。您已在dbo架构中创建了一个存储过程，并期望它根据租户的默认架构执行SQL。
You have a few options
你有几个选择
- Pass the schema to your stored procedure as a parameter and use dynamic SQL - How to pass schema as parameter to a stored procedure in sql server?
- 将模式作为参数传递给存储过程并使用动态SQL - 如何将模式作为参数传递给sql server中的存储过程？
- Create a version of the SP for each tenant e.g.
tenant1.SP_Name,tenant2.SP_Name - 为每个租户创建一个SP版本，例如tenant1.SP_Name，tenant2.SP_Name
- Don't use stored procedures.
- 不要使用存储过程。