1
  • Windows 2008 R2
  • MSSQLSERVER 2012 (SP1) Standard

When running sp_addsubscription under account within db_owner role of both user and distribution databases, I get the following error:

Msg 14260, Level 16, State 1, Procedure sp_MSadd_subscriber_info, Line 44 You do not have sufficient permission to run this command. Contact your system administrator. Msg 14042, Level 16, State 1, Procedure sp_MSrepl_addsubscriber, Line 342 Could not create Subscriber.

...despite books online (https://msdn.microsoft.com/en-us/library/ms181702.aspx) stating that “…db_owner fixed database role can execute sp_addsubscription”.

sp_addsubscription appears to kick off another sys procedure called sp_MSadd_subscriber_info which requires sysadmin:

-- Security Check IF IS_SRVROLEMEMBER ('sysadmin') != 1 BEGIN -- "You do not have sufficient permission to run this command." RAISERROR(14260, 16, -1) RETURN 1 END

Therefore, sp_addsubscription cannot be run without sysadmin seemingly.

I've also recreated this on a Windows 2012 R2 machine running MSSQLSERVER 2012 (standard).

However, on a Windows 2012 R2 machine running MSSQLSERVER 2014, the issue does NOT present itself.

Cannot find any information online on this. Is this a bug with earlier versions of windows / SQL does anyone know? Alternatively, am I missing something?

0

This is resolved by ensuring distributor_admin (or login it is replaced by) is in sysadmin server role

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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