Multiple database users, singular objects and procedures

[CatPlus]

Hello:

For purposes of this example, lets say I have a table called DIRECTORY. I will create an ASP script to ADD, MODIFY or DELETE records therein. I will create a stored procedure that will list all records contained in DIRECTORY

WISH LIST: Each user would be assigned an account and a login name and password. They can perform all functions but limited to their records only. And let's say I have 10 independent users.

I do not want to replicate the tables and procedures 10 times. Besides, when make changes, I want to ensure that those changes are available to everbody. For instance, if there is a ZIP object (or field) with 5 text characters and now I want to make it 10 characters, I would want this change reflected in all the 10 users account. When reports are run, the output of the fields will of 10 characters and not 5 ... in all databases

QUESTION: How is this done?

Thanks for your help and suggestions

Mickey Shekdar

 

[SQLSister]

OK, as best I can interpret it you have differnt users who you want to see only their own records and you want the solution that will make maintenance the easiest. To my mind here are the ways you can do this.

Set up ten separate databases and then use scripts to update all of them when a change is made.

Set up transactional replication with filtering so each subscriber only sees and updates their own data.

Set up one database with views to filter the data by user. If you don't want to have separate stored procedures for each user, you will need to use dynamic SQL to specify which view to use.

Suspect in your case the tranactional replication is the proper choice, but it is the most complex to set up and keep operating.