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
Best Answer
I just reviewed the replication on one of my clients systems and I see similar linked servers both on the publisher and the distributor. In my case the linked servers are all setup using the "Be made using the login's current security context".
If you've got the linked servers setup using a specific security context that's probably because the machines aren't on the same domain. If they are, and the rights are setup correctly you can try changing things. The linked servers should only be used when making changes to the replication configuration so if there's a problem you may not see it until later.