question

rehaan avatar image
rehaan asked

Trigger Permissions

I have a trigger in Database A on table A which does an update in database B on table B. But it is failing due to permissions. When i do an update on Table A in database A, it should do an update on table B in database B. Table A is being updated by application for which i don't know which service account does the update on table A.In this case what permissions do i need to provide on the trigger so that it successfully updates on Table B when table A is updated. Any help would be much appreciated
updatetriggerpermissions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered
You will need to find out which login is updating Database A, then apply the necessary permissions to Database B. Unless of course you want to grant every login write permissions to Database B? Are triggers what you really need, would replication not be a better option? Without some context about the actual needs would be had to say which would be better.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

With replication you can modify the underlying replication procedures for update on the particular table, and also set a filter on the article. If the DB's are on the same server I would probably opt for a trigger instead of replication, but then your back to finding out the permissions you'll have to grant. Usual thing with SQL many ways to do a task, but finding the right one is best.
1 Like 1 ·
Thanks Anthony for the response. If i want to update only 2 columns on Table B in database B , how can i do that with replication I might not have every row in Table B which exists in Table A
0 Likes 0 ·
Thanks Anthony. I implemented Replication as you suggested
0 Likes 0 ·
jasmy avatar image
jasmy answered
You can use context_info. I f you want to invoke trigger for specified login then you can use the contetxt_info Please see http://www.sql-server-performance.com/2008/how-to-use-context-info/
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Jasmy. i will see how it will help me
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.