Authentication in a trigger
-
I am attempting to write a trigger that prevents changes to a table. My logic is as follows:
1) Check whether the user is a certain user on our domain.
2) If the user is this user, allow the transaction.
3) If the user is NOT this user, check to see if the user is the sa.
4) If the user is the sa, allow the transaction.
5) If the user is not either of these users, block the transaction.
I wrote the following code in order to attempt to implement this logic:
CREATE TRIGGER AuditUpdate_AuditTrail_BbalTxn
ON AuditTrail_BbalTxn
FOR UPDATE
AS
BEGIN
IF (system_user!='DOMAIN\username') OR ( system_user!='sa')
BEGIN
RAISERROR 50001 "You are not authorized to make changes to this table."
ROLLBACK TRANSACTION
RETURN
END
END
My problem is that the system seems to be checking whether BOTH of these conditions are true before allowing the transaction. I was logged on to the database using the specified NT account, but it still did not allow the transaction. How can I rewrite this so that it allows the transaction if EITHER condition is true?
“If you're not outraged at the media, you haven't been paying attention.”
-
I think it is simply a case of changing your logic.
Try changing the OR to an AND then it should only rollback of the user is not DOMAIN\username AND not sa.
-
Yes, makes one think of de Morgan boolean algebra
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply