none
Log Reader agent account RRS feed

  • Question

  • can i write a script which will check if replication log reader agent is running under SQL service account if not then modify it to run under SQL service account.

    please help on this


    SQL Server DBA

    Wednesday, October 12, 2016 5:38 AM

Answers

All replies

  • Iam not much on replication ,but for your question you need to go through the below as per BOL to see anything abnormal breaks.

    Log Reader Agent Security
    https://msdn.microsoft.com/en-us/library/ms189405.aspx


    View and Modify Replication Security Settings
    https://msdn.microsoft.com/en-us/library/ms151761.aspx

    Regards, S_NO "_"

    Wednesday, October 12, 2016 6:49 AM
  • I will recommend not to do such things on automation as there are times when intentional changes are done which are required and after changes, services restart may be required so, keep it like that. However you you can use script to know the status of which service is being used.

    Also, some good link on it, to check to get more better aspect of it.

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

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

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


    Santosh Singh

    Thursday, October 13, 2016 7:25 PM
  • If you really want to do that, you can try the following script

    USE msdb;
    GO
    
    DECLARE
    	@logReaderAgentJobName nvarchar(128),
    	@jobID uniqueidentifier,
    	@stopExecutionDate datetime;
    
    SET @logReaderAgentJobName = N'YourLogReaderAgentJob';
    
    SELECT @jobID = [job_id] 
    FROM [dbo].[sysjobs] 
    WHERE [name] = @logReaderAgentJobName;
    
    IF @jobID IS NOT NULL 
    BEGIN
    	SELECT @stopExecutionDate = [stop_execution_date] 
    	FROM [dbo].[sysjobactivity]
    	WHERE [job_id] = @jobID;
    	
    	IF @stopExecutionDate IS NOT NULL OR ISDATE(@stopExecutionDate) = 1 
    	BEGIN
    		EXEC dbo.sp_start_job @logReaderAgentJobName;                           	
    	END
    END


    A Fan of SSIS, SSRS and SSAS

    Thursday, October 13, 2016 8:49 PM
  • To get a list of Log Reader agents running under the SQL Agent account you will need to do this:

    select  name from MSlogreader_agents where publisher_security_mode=1
    and publisher_login=''
    
    

    To change the account you will need to do this:

    sp_changelogreader_agent 'NewLogin','NewPassword'

    This must be run in the publication database.

     

    Thursday, October 13, 2016 11:32 PM
    Moderator
  • To get a list of Log Reader agents running under the SQL Agent account you will need to do this:

    select  name from MSlogreader_agents where publisher_security_mode=1
    and publisher_login=''

    To change the account you will need to do this:

    sp_changelogreader_agent 'NewLogin','NewPassword'

    This must be run in the publication database.

    And to list log reader agent which is not running under SQL Agent service account:

    use msdb;
    select SJ.name [Job Name], JS.step_id [Job Step Id], SP.name [Proxy Name], SUSER_SNAME(SP.user_sid) [Login]
    from dbo.sysjobs SJ inner join dbo.sysjobsteps JS 
    on SJ.job_id = JS.job_id
    inner join dbo.sysproxies SP
    on JS.proxy_id = SP.proxy_id 
    where  SJ.category_id = 13;
    GO



    The idea is that if it’s not under SQL Agent service account, it must be running under proxy.

    If you have any other questions, please let me know.

    Regards,
    Lin


    Friday, October 14, 2016 8:33 AM
    Moderator