Hi,

I use sql server 2005 express with Studio Management express.

Each windows-account in the domain in our organization who starts the application creates automatically (in code-behnd) first his own databaae (with as name his unique number in the organization), then several tables.

This happens only the first time he starts the application. So each member has his own database and tables.

We use IIS 5.1, so the account which runs under asp.net is ASPNET.

My question is: which privileges do i have to give to 'ASPNET' in order to create databases and tables and how to do that in Studio Management? I tried with db_reader and db_writer, but this is obviously not enough.

Another aspect is: how to protect each database from all other users, because there all are created with ASPNET. But Remote Connection is set to Local and the users reach their db via aspx pages, so i don't think it's a real issue ..

Thanks for help and other suggestions.
H.

Thanks

I would imagine that the user needs some reasonably high privileges to create a database. Creating objects in the database itself should only require DBO access, but first you need to create the DB.

However, this design screams maintenance nightmare! Do you have a compelling reason to use a separate database for each user? Why not make the tables in a single database have a user identification field in them to separate the user's data?

-Peter

Hi, thanks for replying.

The application runs surveys. Each user can create his own survey and collect the results. That's why when a user starts the application, the application first create a database, then some tables. This happens only the first time. It's easier, i think, to work like this instead of one database with a multitude of tables in it.

But i need to know which privileges to give to ASPNET for creating the databases.

There is sql server role called "dbcreator". I would venture a guess that would work.

Why would you need a multitude of tables? (And how technically would that be different then a multitude of databases?) All you need is design your tables with a user id field. You don't need any more tables that what you have, they just need to be designed to accommodate multiple users.

-Peter

Ok, thanks

I would go for a single database and create user's survey entry in a table that would uniquely identify each user. Creating different databases, different tables for each user in the system would defeat the purpose of relational databases.