Could not log history/error message. Only members of the sysadmin fixed server role can perform this operation

Question

I have a job setup that copies transaction logs for log shipping purposes.

It seems to be working, I think, but i cant verify that because of the following error:

2021-08-22 21:00:00.86 Starting transaction log copy. Secondary ID: 'c29f....'
2021-08-22 21:00:00.87 Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
2021-08-22 21:00:00.87 Error: Only members of the sysadmin fixed server role can perform this operation.(.Net SqlClient Data Provider)

I checked permissions, and clearly Im sysadmin...

So why is it complaining?

Answer 1

I resolved this by reverting the SQL Server service and agent services logon creds from my local account to the default NT service accounts per this post.

Strange since my AzureAD logon account is part of sysadmin...

Answer 2

Good day,

I checked permissions, and clearly Im sysadmin

You checked your permissions but the Job is not executed by you by default (unless configured such). My guess is that it seems like the SQL Server Agent’s startup account not being a sysadmin. If you have for example the monitoring server on a separate machine then the sa in one machine is not the same user as the sa in the second machine.

To be sure about your specific issue we need more information from monitoring the execution. In this post you can see example of solution and more important he speak about how he monitored it which can help provide more information if this did not solve your needs

https://blog.sqlauthority.com/2019/10/21/sql-server-log-shipping-error-only-members-of-the-sysadmin-fixed-server-role-can-perform-this-operation/