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
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.
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.
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/
19 People are following this question.
