How to create a User Specific Database In SQL Server

Question

1.00/5 (1 vote)

See more:

, +

I have database in SQL server, I can connect to web app to use it, but I want this web app to create a copy of this database for each user on SQL server so that each user will have his own database. This Pic shows what I want to say.
Question is that will I have to work in web app to send command to SQL Server to create database for user or I will have to code on SQL Server side?
Thanks in advance

Posted 9-Jun-14 10:57am

Sherkhan3451

Updated 9-Jun-14 11:27am

PIEBALDconsult

v2

Add a Solution

Comments

Sherkhan3451 9-Jun-14 16:58pm

sorry, its the link, https://dl.dropboxusercontent.com/u/107638377/User%20Specific%20Database.png

Sergey Alexandrovich Kryukov 9-Jun-14 17:21pm

You should better move it from comment to the question using Improve question. Use the HTML anchor tag.
—SA

Sergey Alexandrovich Kryukov 9-Jun-14 17:20pm

This is way too usual task for database modeling so it's hard to see what could confuse you. You should have 'users' table, and some data elements would use the keys of this tables as foreign key, and so on.
Not clear what do you mean by "code on SQL server side". It's all just SQL, you use it via ADO.NET...
What's the problem?
—SA

Sherkhan3451 9-Jun-14 18:03pm

@Sergey Alexandrovich Kryukov I am going to create a "Store Management" website, which will be having database same as Northwind, so when any Store Manager will sign up, he will have his own database created on the server for his business, this way each Store manager can use the same website for his Store, but one manager will not be able to access other's database. For this purpose I have database for one store manager, what if thousands of Store managers sign up, so one database will not server all of them. So what code will be needed when new user will sign up, he will have empty copy of database on server for his use?

Sergey Alexandrovich Kryukov 9-Jun-14 23:51pm

Copy of each database? That would be a big abuse. Why?
Still unclear what kind of help do you want to get. "What code will be needed?" is not any reasonable question. No one will probably volunteer to write code for you, especially with unclear, not defined and probably not reasonable requirements.
—SA

Sherkhan3451 11-Jun-14 15:24pm

Sergey thank you for your valuable suggestions, it taught me some extra things, but the thing or code which I wanted is here now by user AnkitGoel.com.

Sergey Alexandrovich Kryukov 11-Jun-14 16:57pm

Great. Good luck, call again.
—SA

2 solutions

Solution 1

(I can't view that image.)

"create a copy of this database for each user"

That doesn't sound like a very good idea. What are you actually trying to accomplish?

Posted 9-Jun-14 11:33am

PIEBALDconsult

Comments

Sherkhan3451 9-Jun-14 18:04pm

@PIEBALDconsult please read my comment above.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

AnkitGoel.com · Accepted Answer · 2014-06-09T21:01:00

Solution 2

I would suggest you, when a new store manager signs up, run the DB Scripts from your web application.

1. connect to master db and run create database query.
2. connect to database just created and execute all other scripts(create db, create tables, create view, create procedure, create function, etc.)
3. use the database in your web application, as you like.......

Mark as answer if helpful....

Posted 9-Jun-14 21:01pm

AnkitGoel.com

Updated 9-Jun-14 21:04pm

v2

Comments

Sherkhan3451 11-Jun-14 15:26pm

Thank you very much Ankit, I did it with minor changes, like I did not connect master db, I just connected the server, and ran create database query, then connected the new database, and ran other queries of tables etc. Thank you very much again.

AnkitGoel.com 12-Jun-14 6:34am

You are welcome Sherkhan.... Yes, you need not be connect to master database, sql server will connect you automatically to the default database associated with the login user. Which is generally master db itself.