Member Avatar for zachattack05

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?

  • 2 Contributors
  • 2 Replies
  • 113 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by zachattack05

Recommended Answers

All 2 Replies

Member Avatar for lolafuertes

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

Member Avatar for zachattack05

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.