- 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?
