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

Even though there are many disaster recovery solutions available in SQL Server as of today, few of my clients Still go with log shipping. In this blog, I would share my learning about one of the errors which I observed with one of my clients. In this blog, we will learn how we can fix a specific log shipping error about the sysadmin fixed server role.

My client contacted me and informed that their COPY and RESTORE jobs were failing. I asked them to share the exact error message from the job history. The following piece of information was useful.

*** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Only members of the sysadmin fixed server role can perform this operation.(.Net SqlClient Data Provider) ***

I checked documentation about the SQL Server Agent Service account and found this link.

On that page under the “SQL Server Role Membership” section, it is mentioned that we need the Sysadmin role for the SQL Agent service account. Hence, the error message is straight forward – the SQL Agent account is not having sysadmin permission. We checked the primary and secondary server and it was a SysAdmin in SQL Server.

I asked them to capture profiler on all servers involved and they provided me three traces while running the job. I was surprised to see three and I called them again. They informed us that they have a monitor server also. The profiler trace from the monitor server indeed showed an error message.

WORKAROUND/SOLUTION – Fixed Server Role

In this case, the error was due to permission on the monitor server. After giving Sysadmin permission to the SQL Server service account on the monitor server, the job started executing like a charm. If you see this error, you can run the below command to verify the permission.

Select IS_SRVROLEMEMBER ('sysadmin','domain\sql-agent-service-account')

If this returns zero, then the account is not a sysadmin and error would be fixed by giving sysadmin permission.

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