Detailed Permissions Required for Replication Agent User on MSSQL2012

[Former Member]

Our proposed Replication Landscape is as follows:

- Replication Server on Windows 2008 R2, Rep Server 15.7.1 SP207 (RSSD running on 15.7 SP134)

- Replication Agent on Windows 2008 R2, Replication Agent 15.7.1 SP208 (Primary DB is MS SQL 2012)

Replication Server + RSSD and Replication Agent have been installed on their hosts, and the next step is create the Replication Agent Instance. To do this, I have created a Resource File to automate all the inputs. This includes the details for the Primary Database user, SAPRepAgt.

When I run ra_admin -vr name_of_resource_file.rs, I get the following error:

ERROR: User ID <SAPRepAgt> for primary database does not have sufficient privileges -- invalid option: initialize_instance=no

The MS SQL 2012 database is managed by a separate team with a high-focus on security. They will allow the user to have db_owner role membership, but will not allow sysadmin Server Role to be granted to the user. We are trying to fine-tune the access via custom Role so that we can get the access we need without requiring sysadmin role. However, we are having difficulty finding and assigning permissions from the guides in MS SQL 2012.

Is there anyone who knows how to customize a Server Role in MS SQL 2012 so that Replication Agent can work, or is sysadmin the only way?

Permissions	When required
create table	To create tables in the primary database.
create trigger	To create DDL triggers in the primary database.
create procedure	To create procedures in the primary database.
create role	To create the "ReplicationAdmin" role. Only the user with the "ReplicationAdmin" role can mark a table or procedure.
db_owner role	To allow Replication Agent to execute sp_repltrans and sp_repldone in the primary database. This role is also required for primary database initialization.
grant	To grant select permission on sys.sysschobs to the "ReplicationAdmin" role.
sysadmin role	For Microsoft SQL Server data server initialization and deinitialization using pdb_xlog init and pdb_xlog remove, respectively.

[former_member285566]

Hi Douglas,

According to both SAP and MS product manuals,  ra_user requires sysadmin role to run sp_replicationdboption in MS server to setup a database replication for SAP RAM. Only members of the sysadmin fixed server role can execute sp_replicationdboption.

SAP Docs

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00269.1571200/doc/html/bde1279427627...

The first time initialization requires sysadmin role to install server-level objects for replication logging. Subsequent Initialization of the same database or other databases in the server requires the role removes and reinstalls some system tables in the primary databases.

At initialization, Replication Agent creates some system tables in the primary database using the Microsoft SQL Server sp_replicationdboption stored procedure. These tables are also removed with sp_replicationdboption when the Replication Agent ra_admin deinit command is used. Do not modify these tables directly. For more information about sp_replicationdboption, see the Microsoft SQL Server documentation.

MS SQL Server Docs

https://msdn.microsoft.com/en-us/library/ms188769.aspx

sp_replicationdboption is used in snapshot replication, transactional replication, and merge replication.

This procedure creates or drops specific replication system tables, security accounts, and so on, depending on the options given. Sets the corresponding category bit in the master.sysdatabases system table and creates the necessary system tables.

To disable publishing, the publication database must be online. If a database snapshot exists for the publication database, it must be dropped before disabling publishing. A database snapshot is a read-only offline copy of a database, and is not related to a replication snapshot.

Permission

Only members of the sysadmin fixed server role can execute sp_replicationdboption.

Regards,

Binh Liu