Update,Insert or Delete on tables at subscriber fails in transactional replication

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 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’))

       

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s