Executing stored procedures under a different database context?_问答_开发者

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 stor开发者_开发技巧ed 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.

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.

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

Regarding your first approach: you can reference tables in different database from which the stored procedure is running. You just need to fully qualify the table name [databaseName].[schema].[tableName].[columnName]. There is no need for building a string of dynamic SQL.

I'm not sure if you mean that each customer has their own separate database, or separate database server. If the former, then the answer above would seem obvious: reference tables via the DatabaseName.SchemaName.TableName syntax (or just DatabaseName..TableName if you always use the same schema.) If the latter, then you will need to Google for exotic solutions for accessing stored procedure code from a different server.

Either way, to me this cries out for a better automated deployment solution, rather than trying to execute stored procedures across database boundaries. You could setup your stored procedure deployment solution to allow you to quickly deploy new versions of a stored procedure to all databases, or just a subset. What if the customer requests a working self-contained copy of their database? This seems to me a much better solution.

Duplicating code is not always a bad thing.

Super old post, but you could suffix your custom stored procs with a ClientIdentifier?

dbo.sp_getOrders  
dbo.sp_getOrders_xyz123

Then every standard proc has a ClientIdentifier param that would attempt to call the custom proc, if it exists.

Then all your code is in one DB, with one set of source code, any customisations are obvious (and who they belong to), deployment and source control is simple.

depending on clients access to their databases, I'd probably obfuscate the clientId...