I'm in the process of creating an application where there is a central 'global' database which contains all the aspnet Membership and Role info as well as info which is global to the application as well as multiple 'client' databases each responsible for containing the individual client's data.
Each of the client DB's will be identical in schema (tables, stored procs, etc) but will have different permissions:
User1 is permtted to update ClientA and ClientC and should therefor have access to client database A & C
User2 is permitted to update only ClientB and only has access to database B
User3 is permitted to update all clients and therefor has access to all client databases.
What I'm trying to work out is how best to achieve the accesses to the individual databases. The intended process for handling user access would be the following:
User Login >
User redirected to main user page >
User selects client to work with >
User redirected to client's main page
One of the main thoughts I had was to stored the connection string for the selected client in a session variable which is specific to the users session however I'm wondering if there is a better way.