Adding columns to a replicated table

When you add tables to transactional replication and you want replication to propagate column additions made by third-party applications, you can run into a permission problem.

By default, replication requires the user who alters the table and adds a column to be either sysadmin or db_owner.

You may well have applications that do not hold those permissions and still need to add columns to replicated tables.

To work around this, we can bend the database DDL trigger that fires on ALTER TABLE, adding the EXECUTE AS SELF option to it. When you make that change you must be sysadmin or db_owner yourself: EXECUTE AS SELF makes the trigger run under the same execution context as the one you had when you altered it.

Microsoft protects that trigger from modification with a second trigger, which you simply disable before the change and re-enable afterwards.

Use the following code to alter the tr_MStran_altertable database DDL trigger and add the EXECUTE AS SELF option:

USE [database_name]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

DISABLE trigger [tr_MStran_altertrigger] on database;
GO

ALTER trigger [tr_MStran_altertable] on database
WITH EXECUTE AS self
FOR ALTER_TABLE
as

    set ANSI_NULLS ON
    set ANSI_PADDING ON
    set ANSI_WARNINGS ON
    set ARITHABORT ON
    set CONCAT_NULL_YIELDS_NULL ON
    set NUMERIC_ROUNDABORT OFF
    set QUOTED_IDENTIFIER ON

    declare @EventData xml
    set @EventData=EventData()

    exec sys.sp_MStran_ddlrepl @EventData, 1
GO

ENABLE trigger [tr_MStran_altertrigger] on database;
GO