I'm working on a system at the moment which, for no apparent reason, has been split into no fewer than 7 separate databases so I'm looking to merge everything together. The application is used within the client's company but a restricted version is also exposed to the internet.
At the moment, a separate 'internet' version of the database exists which is essentially a set of (partially restricted) views and a copy of all the stored procedures located in the main database. I'm looking to move the views back into the main database under a separate schema and provide the appropriate user accounts with access to one of the schemas ('core' or 'inet' in this case) to control permissions.
My problem is that I want to use a 'shared' set of stored procedures and had assumed that since each user has a default schema that I could use this to access either the view or table (depending on which user logged in). Unfortunately this doesn't seem to be working (Invalid object name error message). Can anyone shed any light on whether or not this is even possible - I'd really rather not have to replicate all of the stored procedures across each schema if at all possible...