SQL SERVER – Log Shipping Error: Only Members of the Sysadmin Fixed Server Role Can Perform This Operation

Even with new features available in SQL Server, there are still situations when log shipping is preferred and used by many organizations. After a long time, one of my clients involved me with an interesting error in log shipping. They have observed that Log Shipping Copy and Log Shipping Restore jobs have been failing with log shipping error.

I checked the history of jobs and found below:

Log Shipping Error

Message
*** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Only members of the sysadmin fixed server role can perform this operation.(.Net SqlClient Data Provider) ***
—– END OF TRANSACTION LOG COPY —–

The ID for the message raised is 21089. So, we captured profiler trace as I wanted to know the exact place where an error was being raised. It didn’t take much time to identify that sys.sp_MSlogshippingsysadmincheck returns error 21089. This was called by

exec @retcode = sys.sp_MSprocesslogshippingmonitorhistory 
                    @mode = 1
                    ,@agent_id = @agent_id
                    ,@agent_type = @agent_type
                    ,@session_id = @session_id
                    ,@session_status = @session_status
                    ,@monitor_server = @monitor_server
                    ,@monitor_server_security_mode = @monitor_server_security_mode
                    ,@database = @database
                    ,@log_time = @curdate
                    ,@log_time_utc = @curutcdate
                    ,@message = @message

So, it is clear that the job is connecting to the monitor server and raising an error.

WORKAROUND/SOLUTION

As a next step, we took a profiler on the monitor server. We confirmed that this issue was caused by the SQL Server Agent’s startup account not being a sysadmin on the monitor server. I think this issue would happen when the monitor server is another server and proper permission is not given to the primary or secondary server SQL Agent service account. This is how we fixed the log shipping error.

Here are other blog posts which you may find interesting:

What are Ports Needed to Configure Log Shipping? – Interview Question of the Week #169

What are Ports Needed to Configure Log Shipping? – Interview Question of the Week #169

• SQL SERVER – Alternative of Log Shipping in Simple Recovery Model
• SQL SERVER – Log Shipping Monitor Not Getting Updated
• SQL SERVER – What is WRK file in Log Shipping?
• SQL SERVER – Log Shipping Copy Job Failure – The Password for This Account Has Expired
• SQL SERVER – Log Shipping Restore Job Error: The file is too recent to apply to the secondary database
• SQL SERVER – SSMS: Transaction Log Shipping Status Report

Reference: Pinal Dave (https://blog.sqlauthority.com)