One database with stored procs and views to dynamically target multiple databases
Posted on 2007-10-13
Is there a way to have a common set of stored procs and views which can run againsts a specified database dynamically. We want to keep a common set of SP and Views in one master DB and have many instances of a database with just tables.
The web applcation (ASP.NET) will run these SPs and views against different databases bases upon who is logged in.
So each client can have their own database full of data tables. Each of the client databases would have exactly the same structure and table names.
Is this at all possible with SQL server 2005, and if so can you point me in the right direction for a solution or further reading.
Thanks. Much appreciated.