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

4 years ago

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