dmw (8/12/2009)
Thank you Paul and Doug for your responses.As Paul said, I was wrong when I stated that schemas do not contain stored procedures.
I suspect tablespaces are irrelevant to my immediate problem. They're just concerned with where the data is stored (i.e. controlling the physical files) and not how it is organised. True?
So schemas may be the answer but we are constrained by the schema=user identity.
If we are using OS authentication, a user has only one identity. So, if I want to 'create another schema' for a user, I must generate a pseudo user/schema and then grant access to my original user.
My alternatives based on further research seem to be
(1) Virtual Personal Databases. This ORACLE feature comes pretty close to meeting my needs BUT is only available with the top-end Enterprise Edition.
(2) Give up, go for a single database and add a CatchmentID column to every table. This seems to be the approach favoured by Tom Kyte of ORACLE (see, for example, http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:31263903816550).
(As an aside, you couldn't exactly describe that guy as good-humoured!).
It does seem very strange that a top-end database, such as ORACLE, puts so many barriers in the way of implementing a solution which comes naturally to MSSQL. Maybe, their approach is the most efficient one but it would be nice to have a choice.
I've done it before. In Oracle, each "database" would be a separate schema. Each schema sees only its own stuff.
I would totally disagree with Tom Kyte's solution of adding a CatchmentID key to each table. If you do, your schema is now "multi-tenant".
As a side note SQL Server is the odd animal with it's multiple database component to separate schemas. The original relational model done by IBM, Oracle, and others in the 1980s did not have it. Everything was based upon schema owner. SQL Server started real support for multiple schemas within a single database in SQL Server 2005.