How to create multiple personal tables on the fly for each registered user of a website using VW

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

In SQL Server you will need to create a schema for each user name to get the effect that you want, for example:

CREATE SCHEMA [user name]
AUTHORIZATION [user name];

CREATE TABLE [user name].Table1
(id int);

Then you can do things like:

SELECT * FROM [user name].Table1;

There are a number of variations of how you might do this, but this is a simple example of the basics.

Dan

|||

OK, doing some extra research in Server Books Online 2005 Express I found the same info. But here's my problem: How to do it dynamically.

Here's the full scenario:

1) I create a user in my web application using the default database ASPNETDB.mdf created by using the login controls in ASP.NET 2.0 (all goes well so far)

2) I then want to create the complete set of tables dynamically for the user that was just created, in a different database (e.g. APPDB.mdf)

So far, I think I have to tackle it this way, but I don't seem to be able to figure out how to actually make it work (or find out if it's really possible at all):

- Use the username for the user just created to create a USER in SQL Server. I would like to do this using a stored procedure, so I can put in the username as a parameter and have the USER created automatically as soon as the user is created in ASP.NET

- Then, I would have to create the SCHEMA in SQL Server, so that the tables I create in this SCHEMA (seems one can create the tables at the same time as you create the SCHEMA, according to Server Books Online) are named [username].Table1 etc.

- After this, there would have to be a way to make sure that when the user logs in each time he gains control over his/her own tables in the database, i.e. the Database Username would have to be linked to the ASPNET Username.

Any Thoughts? All are wellcome...

DJR

sql