Permissions on objects accessed inside a trigger.

I have a trigger in table A, the trigger updates tables C and D and it also have do a call to an the stored procedure which updates and do insertions on tables X and Y.

- Do I need to specify additional permisions for update/insert on Table C and D for those users who have access to table A ?
- Do I need to specify additional permisions for update/insert on Table X and Y for those users who have access to table A, because those tables are updated through a stored procedure ?

Thanks in advance,
Who is Participating?
jdlambert1Connect With a Mentor Commented:
Okay, I was completely wrong. I haven't had that problem because all my objects are owned by dbo.

Either the tables all have to have the same owner, or the tables down-stream from the first table (the one with the trigger on it) have to have appropriate permissions (insert/update/delete, depending on the trigger's or sp's actions) for the owner of the first table. Otherwise, it's a "broken ownership chain".
You don't need to specify additional permissions.
fischermxAuthor Commented:

So, I don't need permissions for any, any, any thing ussed inside a trigger ?
Well, that's what I knew, too, but  why am I getting a INSERT permission denied on object 'MyTableAccessedInsideAStoredInsideATrigger', database 'MyDB', owner 'dbo'  ??

Which special situations would requiere a specific permissions ?

Jan FranekCommented:
AFAIK it depends on who is the owner of all these objects - if all the tables A, C, D, X and Y as well as the stored procedure have the same owner, there shouldn't be any problem with permissions. However, if some of these objects has different owner, you may need to specify some permissions.
fischermxAuthor Commented:

I found the problem.
The table with the permissions problem was being accessed through dinamyc SQL using Execute(@MyStmt).

Reading the same table outside of it, just some lines before that call, didn't cause any problem, though.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.