I have three servers: P1, A1, and R1. P1 and A1 are production oltp servers. On each there are two databases that are being replicated down to R1 for olap (mostly SSRS reports). I am working on cleaning up our linked server infrastructure. P1 and A1 both have linked servers to R1, and SSMS won't let me remove them because they are being used for replication.
Currently both of these linked servers are using the sa account on R1 for authentication. I'd like to replace this with a least privileged account. What privileges would P1 and A1 need in order for replication to work?
In case it matters, all of the publications are transactional replication.
Also, sql server versions:
- P1: 10.0.5500
- A1: 9.0.5069
- R1: 10.0.4000