May 26, 2002 at 12:50 am
I am developing an application in VB which requires different users to share one database. I want my database to be accessible to different users. But, the master table should be available
to "Administrator" only. All the users should work on their copy of table with their set of records. Changes done by other users should be recorded in their copy of records, and should not disturb the master table.
How to do that ?
Rohit.
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach
May 26, 2002 at 5:59 am
You can accomplish that by have each user "own" a table. That means either NT authentication or a separate sql login for each user, plus create table permissions for those logins. This could get unwieldly. An alternative approach is to implement row level security, the simplest method would be to store the login of the owner in each row.
Andy
May 26, 2002 at 11:33 am
Andy,
Since I am new to SQL Server 2000, I am not following the row level method. Please decribe it how it can be done. If I create a new user with his rights, then changes done by this user will affect the original table, or SQL Server keeps track of the changes.
Rohit.
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach
May 26, 2002 at 12:02 pm
You have to do the work, SQL isn't any help. One decision you have to make early on is whether you'd have more than one person allowed to edit a row (excluding your "administrator"). If it truly will be one person per row, you add a userid/ownerid/whatever you want to call it column to your table. When your app queries, it restricts actions to rows matching the current user. In essense it adds "and userid=xyz" to every query against that table. For insrts you have to populate it yourself. This gives you user level security AND lets your administrator easily access all the data from within the app.
Andy
May 26, 2002 at 11:21 pm
Thanks Andy,
I will try your method, and come back to u.
Thanks a lot.
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply