Design issue

Hi

I need some design info. Say that I need to create an educational lab
for databases with SQL Server. I need all the users to be able to
execute common SQL commands ( create - alter drop table , insert ,
update , delete ) and each user have his own space ( users not able to
see others data and a limit say of 2 MB) . The most obvious solution
is to create ( if I have 100 users ) , 100 login account ( SQL Server
Accounts ) , then create 100 databases , and assign each user to one
of this account with database role db_datawriter. Is this the best
solution or there is another way to do it ?

Thanks"db_happy" <database@.freemail.gr> wrote in message
news:b625e551.0407110220.540e1260@.posting.google.c om...
> Hi
> I need some design info. Say that I need to create an educational lab
> for databases with SQL Server. I need all the users to be able to
> execute common SQL commands ( create - alter - drop table , insert ,
> update , delete ) and each user have his own space ( users not able to
> see others data and a limit say of 2 MB) . The most obvious solution
> is to create ( if I have 100 users ) , 100 login account ( SQL Server
> Accounts ) , then create 100 databases , and assign each user to one
> of this account with database role db_datawriter. Is this the best
> solution or there is another way to do it ?
> Thanks

If you need to limit the users' space, then individual databases are
probably the only way to go, since there's no way to manage space quotas
within a single database. (At least, there's no standard way without using a
third party tool or building your own quota tools.) Note that db_datawriter
cannot create objects, so you would need to add the user to db_ddladmin
also, or create a custom role with the permissions set you want.

The downside of this approach is management of the large number of databases
and the associated files, backups etc. However, if you always use standard
scripts for creating/dropping each database, leverage Windows groups for
authentication (if possible), and perhaps use SQL maintenance plans for
backups, then you should be able to keep the work required to a minimum.

Simon|||Hi

You could buy each a copy of developer edition and let them run it on their
own PCs! A cheaper solution would be to use MSDE.

John

"db_happy" <database@.freemail.gr> wrote in message
news:b625e551.0407110220.540e1260@.posting.google.c om...
> Hi
> I need some design info. Say that I need to create an educational lab
> for databases with SQL Server. I need all the users to be able to
> execute common SQL commands ( create - alter - drop table , insert ,
> update , delete ) and each user have his own space ( users not able to
> see others data and a limit say of 2 MB) . The most obvious solution
> is to create ( if I have 100 users ) , 100 login account ( SQL Server
> Accounts ) , then create 100 databases , and assign each user to one
> of this account with database role db_datawriter. Is this the best
> solution or there is another way to do it ?
> Thanks|||On Sun, 11 Jul 2004 17:39:20 GMT, John Bell wrote:

> Hi
> You could buy each a copy of developer edition and let them run it on their
> own PCs! A cheaper solution would be to use MSDE.
> John
> "db_happy" <database@.freemail.gr> wrote in message
> news:b625e551.0407110220.540e1260@.posting.google.c om...
>> Hi
>>
>> I need some design info. Say that I need to create an educational lab
>> for databases with SQL Server. I need all the users to be able to
>> execute common SQL commands ( create - alter - drop table , insert ,
>> update , delete ) and each user have his own space ( users not able to
>> see others data and a limit say of 2 MB) . The most obvious solution
>> is to create ( if I have 100 users ) , 100 login account ( SQL Server
>> Accounts ) , then create 100 databases , and assign each user to one
>> of this account with database role db_datawriter. Is this the best
>> solution or there is another way to do it ?
>>
>> Thanks