If I have SQL authentication enabled, and a user (UserA) on a database has permission to delete rows on TableA, but not TableB, and there is a relationship between TableA and TableB, can UserA successfully delete a record on TableA and have the changes (based on relationships set up) cascade across tables UserA has no permission on or does the user have to have permissions on all related tables? (wow that was a run-on sentence...my teachers would be so proud!)

Does that question make sense? I imagine the changes WOULD cascade, otherwise what's the point?

5 Years
Discussion Span
Last Post by zachattack05

If the relation is defined to have cascade triggers on update and/or deletion, then the user must have permissions to update and/or delete in all the child related tables. If the cascade has multiple levels this applies to each level of the cascade trigger.
See this article

There is another appoach using stored procedures ownerchip chaining to circumvent this situation. See this other article with examples on how to.

Hope this helps


Bleh! I was hoping for a different answer. :(

Stored procedures are new to me and to be honest kind of scare me only because they rely on me not making a mistake. With relationships, I only need to use one delete command and boom, the rest is done and no human error is involved.

Thanks for the articles! I'll investigate them a bit closer.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.