SQL SERVER – FIX Error 5120 – Database is in Read Only Mode After Attaching Files

Sometimes you need to know more about permissions on file, service account etc. to fix certain errors. In my recent visit to a customer location, one of the database developers came to me with a problem. He said that when he attaches MDF and LDF file to the SQL instance, attach is successful, but the issue was next to database name it was saying read-only. Let us see how we can FIX Error 5120.

His problem was interesting, so I asked him to follow-up with me. I asked him to go through try below.

USE [master]
GO
ALTER DATABASE [SQLAuthority] SET READ_WRITE WITH NO_WAIT
GO

But he told that this is not helping and he is getting error.

Error 5120

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “D:\MySoftware\Data\Data_Data.MDF”. Operating system error 5: “5(Access is denied.)”.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “D:\MySoftware\Data\Data_Log.LDF”. Operating system error 5: “5(Access is denied.)”.
File activation failure. The physical file name “D:\MySoftware\Data\Data_Log.LDF” may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database ‘Lis’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

The databases failed to attach due to permissions issues and the cases being marked as read only. We were able to resolve the issue by granting SQL exclusive rights to the database files and unselecting the read-only attribute. Here are the steps which helped him.

FIX Error 5120

First step was to find the Service account, which can be done by opening configuration manager.

SQL SERVER - FIX Error 5120 - Database is in Read Only Mode After Attaching Files ro-attach-01

So, in my lab machine, its “NT Service\MSSQL$SQL2016” for SQL Server 2016 instance and NT AUTHORITY\NetworkService for SQL Server 2014 instance. Here are the steps to give permission to files/folder.

  • Right click the database (mdf/ldf) file or folder and select “Properties”.
  • Select “Security” tab and click the “Edit” button.
  • Click the “Add” button.
  • Enter the object name to select as NT Service\MSSQL$SQL2016′ and click “Check Names” button.
  • It would become MSSQL$SQL2016
  • Click “OK” button.
  • Give this service account “Full control” to the file or folder.
  • Click “OK” button.

Then if you try to attach, it would work fine. I was able to reproduce the error by marking files as read-only.

SQL SERVER - FIX Error 5120 - Database is in Read Only Mode After Attaching Files ro-attach-02

Have you faced such permission errors? These are common and sometimes can be hard to even identify what the problem is. Do let me know via the comments section.

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

, , ,
Previous Post
PowerShell – Script to Listing all Backups Taken with SQL Server
Next Post
SQL SERVER – Configuring Startup Options for SQL Server Management Studio

Related Posts

12 Comments. Leave new

  • I just came across this situation, but the issue was I had took the database offline and then detached. I reversed the step and it worked for me.

    Reply
  • Hello,
    I took my database offline and when I went to place it online; I am getting the same errors above, which is detailed below. I have been working on this for hours and the network guys are stuck on this as well. Any help to get this database back online would be really appreciated.

    (Can I take the same steps above to put it online? I do not have access to the security tab since the database is not available. )

    Thank you!

    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “C:\MSSQL_DATA\Log”. Operating system error 5: “5(Access is denied.)”.
    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “C:\MSSQL_DATA\Log”. Operating system error 5: “5(Access is denied.)”.
    Msg 945, Level 14, State 2, Line 1
    Database ‘DP205_Staging’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
    Msg 5069, Level 16, State 1, Line 1

    Reply
  • pls only run administrator on ssms

    Reply
  • The fastest way to take it offline is to run the following script:

    Alter Database [Database_name]
    Set offline with Rollback Immediate
    Go

    //After this the Database will go offline in a matter of seconds
    //Rollback Immediate – helps by removing any transactions made at that moment and closing the connection
    //To place it back Online run the script below:

    Alter database [Database_name] set online;

    Enjoy!

    Reply
  • How can i move to database one drive to other drive can you explain me.

    Reply
  • Thanks! Helped a lot!

    Reply
  • i was taking a database mdf and ldf file from my friend when i’m trying to attch in to my sql server 2014 it display database file is not primary database file what shall i do please help me???

    Reply
  • Radu Fratila
    July 2, 2019 1:54 pm

    Thanks! Helped a lot!

    Reply
  • It worked perfectly! thank you!

    Reply

Leave a Reply

Menu