Best way to create multiple databases

Question

Best way to create multiple databases

Fabian van Schevikhoven

Newbie

Points: 4
More actions
September 13, 2006 at 4:56 am

#170147

I have created a website on which users can create an account. When their account is created, the application needs to create a database (so a seperate database is created for each account). The databases are all the same, except for 1 row in a login table (this row can be added later with a seperate query). I need the quickest/easiest way to let the webapp create the database. I've tried to attach a 'template' databasefile, but this doesn't work.
The only way I see now, is to execute a very large query each time the database has to be created, but this doesn't sound as the easiest way... Hope someone can advise me how I can solve this problem.
Regards,
Fabian

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Why on earth would you want to create a seperate database for each user, I don't know how many users you have but as the numbers increase the amount of administrative overhead will sky-rocket.

Fabian van Schevikhoven Newbie Points: 4 More actions · Answer 2

That's because of security issues. With this application users can create and send invoices to their customers. Because all of their sales/accounts/products/... data is stored in that database, we choose to create separate databases for each account, so no account can evey see invoices/customers/... of other accounts, even when there's a security leak.

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 3

Instead of creating database for every user(which sound so absurd) you can create views for every user and grant permissions on the view. If you have multiple databases then how would any one get consolidated view? Are you going to use cross database joins or distributed partitioned view?

Hope this points you in right direction.

Thanks

Sreejith

Fabian van Schevikhoven Newbie Points: 4 More actions · Answer 4

That's a good idea, haven't thought of that.

From your replies I understand it isn't a good idea to create seperate database, so I'll drop that thought.

The only problem I see with the view-solution is; the users need to update the data as well, guess that's best to be done by stored procedures?

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 5

If its comming from a web app then stored procedures are definately the way to go, just make sure that you build in sufficient safeguards to prevent sql injection attacks.

Fabian van Schevikhoven Newbie Points: 4 More actions · Answer 6

Ok, thank you all very much for your quick (!) answers. Really helped me a lot!

Regards,

Fabian

Jeff Gray SSChampion Points: 10667 More actions · Answer 7

The [model] database is the "template" for creating new databases. If you make changes to model, then new databases will include the changes when they are created.

Although I agree that it is not neccesarily the best way to go, (creating 1 db for each user) it is not unheard of. In fact, and in some cases, it may be an "absolute" business requirement. Understand that maintenance and upgrades will be a bigger chore, as you would have to apply any changes to each database.

In general, the separation of users' data is easily accomplished by other means, as indicated by others' comments in this topic, but I would check with the business owners to see if that will work for them.

jg