question

Cataster-7485 avatar image
0 Votes"
Cataster-7485 asked pituach commented

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

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?

125451-image.png


sql-server-general
image.png (38.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cataster-7485 avatar image
1 Vote"
Cataster-7485 answered pituach commented

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...


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am glad to hear that the issue solved :-)

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered Cataster-7485 converted comment to answer

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/

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@pituach
My monitor server is set to localhost. i dont have a separate machine/server that im using as monitor server.

 @monitor_server = N'localhost'

Localhost has sysadmin for both my account, sa account, as well as the NT Service accounts.


0 Votes 0 ·