TSQL query for transactional replication log reader agent account in SQL Server

Question

What is the TSQL query to fetch transactional replication log reader agent account in SQL Server?

I can get the information in SSMS UI, from: Replication->Local Publication->Pick a publication->Right Click Properties->Agent Security

I'm unable to find this info in any of the system tables in the distribution database.

I need the info for an automation script.

Answer 1

Have a look at the MSsnapshot_agents and MSlogreader_agents tables created in the distribution database. The Snapshot and Log Reader job_id's can be obtained and used to gather information about the job such as which database principal is associated with the job. It appears that account info is also stored in the tables above.

You can use the job_id from the table above to get extra info from the Agent Job, such as database principal.

SELECT    
    JOB.job_id AS JobID,
    JOB.[name] AS JobName,
    PRIN.[name] AS Principal
FROM
    msdb.dbo.sysjobs AS JOB
    LEFT JOIN msdb.sys.database_principals AS PRIN ON JOB.owner_sid = PRIN.sid
WHERE
   JOB.job_id = @X 

Answer 2

Although, I didn't find a straight forward TSQL, I found 2 alternative ways.

Option 1:

Use myPublisheddb
exec sp_helplogreader_agent

The proc returns a table; with some tweaking we can get the required info.

Option 2:

select * from master.sys.credentials

Same here. Returns a table, which can be mined for the right data.