A question about Create Schema in SQL Server

Hi there,
I've a question regarding Schema in SQLServer. If i compare with Oracle, in Oracle there is an option to create multiple "Tablespaces" in ONE single database instance. When a database user is created, it needs to be assigned a specific tablespace. Now whe
n user actually logs in, a new "Schema" is created with the same name as Username in the tablespace assigned and all the operations are performed in this user specific schema.
The advantage is here that in ONE database instance, multiple schemas can be created each of them having same set of tables, views etc. And a specifc user per schema is having access to all objects created in Schema.
I wonder does MSSQL server 2000 has something like this? I see a "Create Schema" option but the documentation did not help me.
I would appreciate if one can help with this scenario. The objective is that in ONE single database instance SHOULD HAVE multile SCHEMAS with identical objects (tables, views etc).
Thanks in advance
The schema concept does not exist, per se, in 2000. But you will be able to
do something like this in SQL Server 2005.
http://www.aspfaq.com/
(Reverse address to reply.)
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>
|||> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
As Aaron mentioned, SQL 2005 will extent this functionality. However, you
can have multiple schema in a single database and/or single instance in all
versions of SQL Server. The script below illustrates how one can do this in
a single database. If you need to isolate default physical object placement
(akin to default tablespace), you can use separate databases within the same
instance. Unlike Oracle, you can have multiple databases per SQL Server
instance.
USE MyDatabase
EXEC sp_addlogin 'Schema1'
EXEC sp_addlogin 'Schema2'
EXEC sp_adduser 'Schema1'
EXEC sp_adduser 'Schema2'
CREATE TABLE Schema1.Table1(Col1 int)
CREATE TABLE Schema2.Table1(Col1 int)
Hope this helps.
Dan Guzman
SQL Server MVP
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>