Change Replication Username and Password

When a replication service account's password changes, every replication agent in the topology needs its credentials updated - and there can be dozens of them. Instead of clicking through SSRS, run this script on the publisher database with the new username and password set at the top. It PRINTs the complete set of commands you need: sp_changelogreader_agent for the log reader, sp_changepublication_snapshot for each publication's snapshot agent, and sp_changesubscription calls (login and password separately) for every push distribution agent.

/*
	Run on the publisher database to generate scripts for changing the passwords of:
		- Log reader agents
		- Push distribution agents
		- Snapshot agents
*/

SET NOCOUNT ON;

DECLARE @Username VarChar(128) SET @Username = 'existing_username'
DECLARE @Password VarChar(128) SET @Password = 'new_password'

--Chane the log-reader username and password.
PRINT 'EXEC sp_changelogreader_agent @job_login=''' + @Username + ''', @job_password=''' + @Password + ''''

--Change the snapshot-agents usernames and passwords.
SELECT
	'EXEC sp_changepublication_snapshot  @publication=''' + Pubs.name + ''',''
	+ '@job_login=''' + @Username + ''', @job_password=''' + @Password + ''''
FROM
	syspublications AS Pubs

--Change the distribution agents usernames
SELECT DISTINCT
	'EXEC sp_changesubscription  @publication=''' + Pub.name + ''',''
		+ '@subscriber=''' + Sub.srvname + ''', @destination_db=''' + Sub.dest_db + ''', @article=''all'',''
		+ '@property=''distrib_job_login'', @value=''' + @Username + ''''
FROM
	syssubscriptions AS Sub,
	syspublications AS Pub,
	sysextendedarticlesview AS Art
WHERE
	Art.pubid = Pub.pubid
	AND	Sub.artid = Art.artid
	
--Change the distribution agents passwords
SELECT DISTINCT
	'EXEC sp_changesubscription  @publication=''' + Pub.name + ''',''
		+ '@subscriber=''' + Sub.srvname + ''', @destination_db=''' + Sub.dest_db + ''', @article=''all'',''
		+ '@property=''distrib_job_password'', @value=''' + @Password + ''''
FROM
	syssubscriptions AS Sub,
	syspublications AS Pub,
	sysextendedarticlesview AS Art
WHERE
	Art.pubid = Pub.pubid
	AND	Sub.artid = Art.artid