October 2, 2006 at 10:46 pm
I want to accomplish the following
I have microsoft access FE and SQL server BE. The tables in SQL are linked via ODBC. I have 15-20 users which belong to different SQL roles and need INSERTS , UPDATES , DELETE permission on a certain table . That is quite staright forward and I have done that. Now the problem is that each role should be able to update and delete only those records which they have created / inserted already, Not every record in the table. How can I do that? Is it possible in SQL server itself, if yes how? or I have to use some FE code?
Please help....
October 3, 2006 at 10:16 am
I don't think you can do it on the Back End unless you write code.
Your design should include a field for the origin of the record, like a foreign key to the role ID from the Roles table. If you are using SQL Server roles it would be a foreign key to dbo.sysusers (SQL 2000) or sys.sysusers (SQL 2005) field UID. Not really sure you can create a real foreign key for sys.sysusers system view, but a reference field would be OK.
Then you can create Front End code or Back End Trigger for Update and Delete. Probably the trigger will be fine.
Also try to post this question on T-SQL form here, they wrice code and may advise you better.
Regards,Yelena Varsha
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply