title: Executing stored procedures under a different database context?
body: <p>Due to security constraints, each customer must have its data stored in its own database. Occasionally clients require customizations that require particular stored procedures within their area to be modified, sometimes significantly. This creates a nightmare for maintaining stored procedures across all databases. It seems like this must be a frequent issue. Are there any well-accepted standards for handling situations like this?</p>
  
  <p>Some approaches I have considered:</p>
  
  <p>Central database containing "standard" stored procedures. Process first checks for the existence of the stored procedure within the client database, if it doesn't exist, executes from the "standard" database. Problem: I can't figure out how to execute a stored procedure from one database and have it reference tables in another database without generating dynamic SQL. This seems like the optimal solution to me as a single stored procedure needs to be maintained, customizations are easily apparent. I just can't figure out how to make it work, if one even can. It always executed under the context of the database containing the SP.</p>
  
  <p>Any customizations must be made to a copy of the standard stored procedure with a _Custom suffix. All stored procedure calls first test to see if a _Custom SP exists, calling the custom one instead of the standard one when found. Customizations are more readily apparent, however every SQL call must be prefixed with another SQL call to verify SP name. Also, any changes to "standard" stored procedures still need to be replicated across hundreds of databases.</p>
  
  <p>Remove stored procedures all together. Store them instead as T-SQL statements either in files or in a table somewhere. Entirely new management system would have to be created to access, test, update the T-SQL code (Management Studio is used for this currently.)</p>
  
  <p>I am looking for input on how to create a simple and elegant solution to this problem, or at least improve upon the current situation, which is to manually update each stored procedure, looking for conflicting customizations as you go.</p>