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

by

Troubleshooting Guide: SQL Server Error 5078 – “Cannot Alter Database Options Because It Is READONLY”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to modify database configuration options while the database is in READONLY mode, which locks the system catalog and metadata from write operations. This typically happens when a database was restored from a read-only backup, placed in READONLY mode intentionally for reporting workloads, or when the underlying filegroup containing the primary data file has its READONLY attribute set at the Windows filesystem level. Additionally, on Windows Server 2025, this can be triggered by NTFS permission inheritance issues or if the database file resides on a storage volume mounted as read-only.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

  • Open SQL Server Management Studio (SSMS) or use sqlcmd to check the database status.
  • Run the following query: SELECT name, is_read_only, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
  • Confirm that is_read_only returns 1, which validates the root cause before proceeding.

Step 2: Check Filesystem-Level Read-Only Attributes

  • Navigate to the physical data file location, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Right-click the .mdf and .ldf files, select Properties, and ensure the “Read-only” checkbox under the General tab is unchecked.
  • Alternatively, run this command in an elevated PowerShell window: Get-ItemProperty -Path "C:\...\YourDatabase.mdf" | Select-Object IsReadOnly

Step 3: Set the Database to READ_WRITE Mode

  • Ensure no active connections are using the database before proceeding.
  • Execute the following T-SQL command in SSMS with sysadmin privileges:
ALTER DATABASE YourDatabaseName SET READ_WRITE WITH ROLLBACK IMMEDIATE;
  • This forces all transactions to roll back and immediately switches the database to a writable state.

Step 4: Validate Windows Service Account Permissions

  • Confirm that the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Modify and Write permissions on the data directory.
  • Open services.msc, locate the SQL Server service, and verify the “Log On As” account matches the account with correct NTFS permissions.
  • Restart the SQL Server service if permissions were changed: Restart-Service -Name 'MSSQLSERVER'

Step 5: Rebuild Database Options via SSMS GUI (If T-SQL Fails)

  • Right-click the database in Object Explorer and select Properties.
  • Navigate to the “Options” page and manually change the “Database Read-Only” dropdown from “True” to “False.”
  • Click OK to apply changes; SSMS will internally execute the ALTER DATABASE command.

Alternative or Verification Steps

  • Re-run the verification query from Step 1 to confirm is_read_only now returns 0, then attempt the original ALTER DATABASE command that triggered Error 5078.
  • If the issue persists, check for Availability Group or Log Shipping configurations that may be forcing the database into a read-only secondary state, as these require failover or configuration changes at the AG level rather than direct ALTER DATABASE commands.