Why Do Replication Deletes Require sysadmin Access

Question

I am running merge replication with SQL 2012.

There seems to be a nasty consequence of the delete triggers added for replication in SQL 2012.

Inside the delete triggers are this,

select @xe_message = CAST('replica_id: ' + convert(nvarchar(100), @replnick) + ', article_id: ' + convert(nvarchar(100), @tablenick) + ', rowguid: ' + case when @article_rows_deleted = 1 then convert(nvarchar(100), @rowguid) else N'0' end + ', generation: ' + case when @is_mergeagent = 1 then N'0' else convert(nvarchar(100), @newgen) end + ', Reason: -1' AS varbinary(1000));
        exec master..sp_repl_generateevent 1, N'Event : ppm_insert', @xe_message

And an error occurs when someone does a delete if they are not in the sysadmin role,

Msg 8189, Level 14, State 10, Procedure sp_repl_generateevent, Line 1
You do not have permission to run 'SP_TRACE_GENERATEEVENT'.

So are you telling me that every user of my database has to have sysadmin access if they are going to be doing deletes?

This seems wrong. Is there a way to turn this tracing off or an alternative way to remove this requirement?

Answer 1

So are you telling me that every user of my database has to have sysadmin access if they are going to be doing deletes?

No, as per BOL's documentation on sp_trace_generateevent:

User must have ALTER TRACE permission.

Answer 2

This is now a known bug (present only in SQL 2012), that Microsoft is currently addressing. Workaround is to give user ALTER TRACE permission until is resolved.

You can track status on Connect.

Answer 3

This issue has been addressed (KB3155209) in SQL Server 2012 SP2 CU12