sql - ASP.NET MVC Multi tenant Application using Single DB Multiple Schema : Calling Store Procedures from the application tries to access dbo tables -

i developing multi tenant application using asp.net mvc, sql server, dapper single db , multiple schema each tenant. tenant assigned db user owns tenant schema. have set of tables in dbo , set shared schema.

say have dbo.tenant , anyschema.table1. application connection string set anyschema user, if call select * table1 returns values anyschema.table1. if have same query in store procedure, throws error tries access dbo.table1.

i have provided execute access dbo tenant db user single sp being shared tenant @ dbo.sp_name

how can execute store procedure logged in tenant db user, accessing table1 of anyschema single sp can used tenants.

assume have 2 users in our database tenant1 , tenant2 own schemas (also called tenant1 , tenant2).

we create contacts table each tenant have tenant1.contacts , tenant2.contacts.

within our application execute following sql using dapper. using tenant specific connection string uses tenant's sql server login details.

select * contacts 

since did not specify schema within sql inferred default schema of user under sql executed. tenant1 query tenant1.contacts , tenant2 query tenant2.contacts.

now we'll create stored procedure in dbo schema executes same query:

create procedure getcontacts begin     select * contacts end go 

note i've not specified schema in stored procedure's sql.

when execute stored procedure our application schema of stored procedure can still inferred current user but sql within sp infers schema of sp. if sp belongs dbo schema execute query against dbo.contacts table.

this happening in situation. have created stored procedure in dbo schema , expecting execute sql against tenant's default schema.

you have few options

1. pass schema stored procedure parameter , use dynamic sql - how pass schema parameter stored procedure in sql server?
2. create version of sp each tenant e.g. tenant1.sp_name, tenant2.sp_name
3. don't use stored procedures.