Executing stored procedures under a different database context?

Question

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?

Some approaches I have considered:

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.

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.

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.)

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.

Answer 1

There is an undocumented back door way to achieve this:

• ensure the procedure name starts with sp_
• place the procedure in master
• mark the procedure as system by executing sp_marksystemobject 'sp_...'

This way the procedure is 'magically' available in every database, and it will always act on the local objects. Eg. if the procedure issues select ... from dbo.Foo and is invoked while in the context of database bar, the effect will be to select from bar.dbo.Foo, even though the procedure was declared in master.

Of course, by using an undocumented procedure (sp_marksystemobject) you expose yourself to all the problems of using undocumented features (might change w/o warning in future release or even on SP/CU update, product support may refuse to help you etc etc).

If I would be in your shoes, I would deploy the procedure(s) on each tenant's DB. The real problem is one of managing changes, my favorite solution is described in Version Control and your Database.

Answer 2

That's when you want to move away from stored procedures, that architecture seems to rely heavily on stored procedures

When clever goes wrong

A better approach would be to move that logic into your domain layer and use composition/polymorphism to address the differences from client to client