I am trying to figure out if it's possible to resolve schemas for tables within a Stored Procedure. If not, I was wondering if maybe I can find a better solution here. Here is my scenario.
I have an application that will access a SQL database that will be used by different organizations within my company. The database will only a have a few tables but it has many stored procedures that will call them. The different organizations will each have their own SQL User and schema with the tables duplicated for each schema. While each schema will have its own set of tables to separate data between the organizations, I do not want to make a copy of each stored procedure since the logic will be the same. I would like the schema to be resolved by the user that is accessing the database. If I do inline SQL within the application it resolves with no problem but using a Stored Procedure will always run it under the owner of the sp, causing it to throw an invalid table name error.
Is there anyway to resolve this or is this just the functionality of MS SQL? Are there any workarounds out there (other than Dynamic SQL) that I can use? Thanks in advance for your help and please let me know if I need to provide more information.
View Complete Post