Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
Thanks a lot. It has been hard to find this info. –  peter Nov 23 '12 at 3:25
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.