Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Allow user to create private DB

Status
Not open for further replies.

BuGlen

MIS
Jun 16, 2000
254
US
I'm working on moving our knowledge base application from an Access frontend/backend solution to a VB.Net frontend / MSDE 2000 backend solution. The MSDE database will be on a central server to which all users can access.

In the current application, the user is able to create private DBs and make it available on the network drive if they wish to share it. Since security is handled by the permissions of the folder in which the DB resides, this was not a major consideration in the current implementation. Now, I need to consider the NT/SQL security model for the migration and create procedures that will allow users to create application DBs and grant access to groups of users.

If anyone has done something similar and can provide some basic information or point me to an online example, I would be most appreciative.

Glen Appleton

VB.Net student.
 
How are these 'private' databases derived? i.e Are they subsets of information that would reside (in the new architecture) in the primary SQL database?

How have they been created in the past (from scratch? - or from queries of one user for another?)

It is quite easy to create ACCESS DBs from a SQL backend (via DTS) even by using a SQL query statement to drive the data selection. Would that suit your requirement? Or is the desire to keep them in SQL? [But, if you keep them in SQLServer, then you face the issue of granting access to the databases. The app that is the front end won't necessarily be agile enough to use differing backend databases]

A little more info is needed to understand where you are giong with this.
 
Hi Ron,

Basically, in the Access app the new DB would be created from a template DB distributed with the application. The template would be copied to the location and name of the users choosing and the appropriate links to the new DB would be maintained in the application using an INI file.

This is the same basic concept I would like to use on the SQL server. A template db would be used and copied to the same server instance using a unique name for the user's db. This much is fairly simple to accomplish. I'm just trying to figure out the best way to set the permissions on the new DB.

Once the new DB is created, the user will need to have the appropriate permissions to grant access to other system users and groups. The admin rights need to be given to the creator of the db, and a default group of DBAs. All of the admin functions will be available from the application interface based on rights. I guess the best comparison would be the implementation of Windows Sharepoint Services (Sharepoint Team Services) where a user is set as the admin of sites they create.

At this point, I'm just trying find out if this function is even possible on SQL Server 2000. I haven't worked very much with setting rights outside of Enterprise Manager and I just don't know if this can be done using SQL commands or if a server app needs to be developed for this purpose.

Any thoughts?

Glen Appleton

VB.Net student.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top