Selective Updates Based On User

We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the
organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.

The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe

What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.

So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.

Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.

So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.

At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.

Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.

At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.

This is for SQL 2000 SP4/Windows 2000 SP4


CREATE TRIGGER audit_mschange
ON dbo.T_PP_Table_2
FOR update AS
IF (UPDATE([Start Dt]) OR Update(shift) or Update
(comments)
or Update(status) or Update (line))
BEGIN

INSERT INTO T_MS_RB_AUDIT

(ord_no,line_tank_old,line_tank_new,shift_old,shif t_new,comments_old,comments_new,status_old,status_ new,trx_dt,trx_username,
start_dt_old,start_dt_new)

SELECT ins.[PP Ord No],
del.line,ins.line,
del.shift,ins.shift,
del.comments,ins.comments,
del.status, ins.status,
getdate(),
suser_sname(suser_sid()),
del.[Start Dt],
ins.[Start Dt]
FROM inserted ins, deleted del
WHERE ins.[PP Ord No]=del.[PP Ord No] AND
ins.autonumber=del.autonumber AND
(ins.status <> del.status OR ins.[Start Dt] <>
del.[Start Dt] or ins.status <> del.status
or ins.comments <> del.comments or ins.line <>
del.line )
END