When you update/insert/delete rows in table in subscriber which is part of transactional replication with updateable subscriber we will end with below error
{
Msg 14126, Level 16, State 3, Procedure sp_check_sync_trigger, Line 28
You do not have the required permissions to complete the operation.
{
Msg 14126, Level 16, State 3, Procedure sp_check_sync_trigger, Line 28
You do not have the required permissions to complete the operation.
Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
Updateable Subscriptions: Rolling back transaction.
Msg 3609, Level 16, State 1, Procedure trg_MSsync_upd_State, Line 133
The transaction ended in the trigger. The batch has been aborted.
}
Above error is raised in sp_check_sync_trigger and according to the below logic in “sp_check_sync_trigger”
If you have a update/delete/insert trigger in a table which is not part of replication (MSreplication_objects) then we raise “You do not have the required permissions to complete the operation.”
{
if not exists (select so.object_id from (dbo.MSreplication_objects as ro join sys.objects as so
on ro.object_name = so.name)
where so.object_id = @trigger_procid and ro.object_type = ‘T’
and (@owner is null or schema_name(so.schema_id) = @owner))
begin
raiserror(14126, 16, 3)
return –1
end
}
Resolution
Identify all the triggers of the table for which you are not able to update/delete/insert rows and identify the trigger which is not part of MSreplication_objects and disable it.
You can use the below query to identify triggers in table which is not part of replication
select * from sys.objects where type=‘TR’ and name not in (select object_name from MSreplication_objects)
and parent_object_id in (select OBJECT_ID(‘Replace your Table here’))
SQLServerScribbles.COM 