Multi-Tenant Architecture - Shared Database/Separate Schemas RRS feed

  • Question

  • We are developing an application on a data model supporting Shared Database/Separate Schemas (multi-tenant with data separation requirement). It would be ideal to have a "single" library of stored procedures, say under dbo, servicing multiple identical tenant schema definitions. I have attempted this using 'synonyms" without any success. It appears that the "synonym" object (stored procedure) does not run queries in the context of the calling schema (even when explicitly told to do so), consequently, generating an error of "object (table) not found". Is this even possible in Sql Server 2005 without using Dynamic-Sql in our stored procedures?
    Wednesday, April 25, 2007 5:20 PM