How to fix Error 2778 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 2778

“Only System Administrator can specify %s option for %s command.”

Why This Error Happens

This error occurs when a login that is not a member of the sysadmin fixed server role attempts to execute a restricted T-SQL command option, such as certain BACKUP or RESTORE statements with privileged flags (e.g., WITH REWIND, PASSWORD, or tape-level device options). SQL Server 2025 enforces stricter role-based access control (RBAC) on these commands because they can interact directly with physical hardware, media sets, or bypass standard data-access permission chains. The root cause is almost always an authorization gap: the executing account has db_owner or db_backupoperator rights, but lacks the server-wide sysadmin privilege required specifically for that command option.

Step-by-Step Resolution Guide

Step 1: Identify the Executing Login

  • Determine exactly which SQL login or Windows account is running the script or job that triggered Error 2778.
  • Check the SQL Server Error Log or Windows Event Viewer (Event ID 2778) to capture the exact command and option being used.
  • Run the following query to check current role membership:
  SELECT IS_SRVROLEMEMBER('sysadmin');

Step 2: Verify Role Membership in SQL Server Management Studio (SSMS)

  • Open SSMS and connect to the instance using an account that already has sysadmin rights.
  • Navigate to Security > Logins, right-click the problematic login, and select Properties.
  • Go to the Server Roles tab and check the box for sysadmin if the account requires this level of access to run the command.
  • Alternatively, run the T-SQL command to grant the role:
  ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\Username];

Step 3: Modify the Script to Remove Restricted Options

  • If granting sysadmin is not an organizational security option (recommended for production environments), modify the script to remove the restrictive flag causing the conflict.
  • For BACKUP/RESTORE commands, remove options like MEDIAPASSWORD or BLOCKSIZE if they are not strictly necessary for the backup strategy.
  • Re-run the job using a standard db_backupoperator role membership instead of sysadmin.

Step 4: Restart the SQL Server Agent Service

  • If the permissions were changed while a job was actively running or cached, restart the SQL Server Agent service to clear the cached security context.
  • Open Services.msc on Windows Server 2025.
  • Right-click SQL Server Agent (MSSQLSERVER) and select Restart.

Alternative or Verification Steps

  • Verification: Re-run the exact command or SQL Agent Job that failed. If successful, check the SQL Server Logs to confirm Error 2778 no longer appears in the history.
  • Alternative: If modifying server roles is restricted by compliance policies, create a specific proxy account in SQL Server Agent with the necessary credentials to execute only the specific subsystem (e.g., Operating System (CmdExec)) required for the task, bypassing the need for a full sysadmin grant on the user’s primary login.