Advise on creating new databases...

[lidds]

I am writing an application in VB.Net, the application stores a number of images within the database and therefore the SQL database size gets quite large. At the moment I use a single database, however what I want to do is create a new database for each project within my application. I need some advise on the best way to proceed with this and also the best way to be able to create a new database for each project. Below is the approach that I was planning to adopt:

1. When the application is installed it automatically has a common database that comes with the application and this database is attached to the SQL Server, lets call this application "AppA" and common database "AppADB"

Within the AppADB there would be a table called projectsTbl and in this projectsTbl it has a 2 columns called "projectDesc" and "projectDB". projectDesc would hold the description of a project and then the projectDB would hold the SQL database name for that project.

2. The user would login to my application and in the admin side of the application have a create project form. In this form he would enter the project description and also a 3 character code for the project. When the user OK's the form it would take the project descrption from the form and put this in the "projectDesc' field in the AppADB and then take the 3 character code from the form add this as a prefix to "AppADB" and put this within the "projectDB" field within the AppADB e.g. if the 3 character code was "SIM" then the projectDB name would be "SIMAppADB"

3. What I obviously now need to do is create a new database called "SIMAppADB" now what I was thinking was I could create a SQL database backup of a database that has the standard database structure that is required and then I can restore this .bak file (which would be in my application installed directory) to this new name, using the following code:

RESTORE DATABASE SIMAppDB
FROM DISK = 'C:\Program Files\AppA Installed Directory\DB.Bak'
WITH REPLACE, MOVE SIMAppDB_Data' TO 'C:\MSSQL\DATA\SIMAppDB_Data.MDF',
MOVE 'SIMAppDB_Log' TO 'C:\MSSQL\DATA\SIMAppDB_Log.LDF'

4. Then when the user logins to my application again, on the login screen he will see a list of projects from the "projectsTbl" table in the "AppADB" database. The user can then select the project they wish to login to and from this the application and extract the database name from the "projectDB" table in the "AppADB" database. I then can store this database name in my application and when the application needs to connect to the database it modifies the connection string to connect to the respective database.

Noew this is where I need the advise, would that be a good way to proceed? Also the user who is creating the new project within my application may not have access to the SQL server, and therefore I need for a normal user to be able to create a new database and be able to attach it to the SQL server. Would they need to know the sa username and password, or could I create a SQL user when my application is installed by the DBA to allow this to be done?

Any help and guidance would be really apreshiated, I hope I have explained this well enough.

Thanks in advance

Simon

 

[gmmastros]

I see no problem with your approach. There are a couple of gotcha's that you need to be aware of.

First, you don't need to know the sa password, or even use the sa account. In fact, you should NEVER use the sa account.

That being said, there are a lot of permissions implications that you need to think about. In order to restore a database you need to be a sysadmin or dbcreator. You can accomplish this using windows authentication or SQL Server authentication. The important thing to realize is that the account must have the fixed server role of sysadmin or dbcreator. You can make any login to SQL Server have these roles.

The other thing you need to be careful about is the 'normal' logins you use. I mean, you could create a login that you only use for restoring a backup, and that's perfectly acceptable. However, you will likely have other logins for when the user is 'just using' your app. If you have the ability to log in, then you probably have the ability to create new users, too. If you restore a backup and then create a login (with permissions to the DB), then restore another backup (for another project), the login you created will probably not have permissions to the newly restored backup. What I'm saying is.... when you restore a backup, you should add some code to make sure that all of your logins have access to the newly restored database.

One last thing to consider.... If this is a commercial application (that you sell to other organizations), you may want to consider installing your own instance. Since you need to have relatively high permissions, installing your own instance will make the dba's at your customer's location a bit happier because sysadmin has permissions to all db's (not just the ones you install and/or use). Even if you don't use a sysadmin account, you would still need to have dbcreator which may make some dba's a little uncomfortable too.

By using your own instance, the system administrators can have better control of their servers while giving you better control of your application. Each instance of SQL Server uses a windows account to start the service. Normally, this is 'Local System' account, but it doesn't have to be. The system administrator could create an account specifically for your application that gives you permissions to certain folders, and that's it. This protects them. Doing this, they are more likely to allow your software to run under the server roles of sysadmin or dbcreator.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lidds]

George,

Thank you for your help, really apreshiated. Just one question, at the moment when my application is installed within a company it is upto the DBA to attach the database and create a user with dbowner privilages for that database, this then allows my application to connect to the database and run OK.

If I was to give this user dbcreator privilages then this would allow my application to restore this database as a different name using this login (assume that DBA allows this) I then obviously need to now allow this user to have dbowner privilages to the newly created database, does the user need anymore priviliages to be added as a dbowner of this new database?

I hope this makes sense, I am not a guru on SQL server so apologies if it sounds a bit simply worded.

Thanks

Simon

 

[gmmastros]

You need to understand the difference between a server role and a database role. Server role applies to the server and ALL databases. Database role applies to a single database.

Server roles are:

sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin

Database roles are:

db_accessadmin
db_backupoperator
db_datareader
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin

I won't go in to the details about what each of these mean. However, if a user is part of the db_owner database role, they can do anything they want to that database.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom