Have you ever encountered a situation where you attach a database in SQL Server and it comes up as read-only? And when you try to change it to read-write, you get an error? This can be a frustrating experience, but don’t worry, we’ve got you covered. In this article, we will explore the reasons behind this issue and provide you with the steps to resolve it.

Let’s start by reproducing the situation. Suppose you have the database files in a file share, and you want to attach the database using the following command:

USE [master]
GO
CREATE DATABASE [ReadOnlyDB] ON (FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB.mdf'), (FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB_log.ldf') FOR ATTACH
GO

After executing the command, you will notice that the database is marked as read-only in the object explorer. This is unexpected and raises the question of why this happened. Now, let’s try to change the database to read-write using the following command:

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

Unfortunately, this command will result in an error:

Msg 5120, Level 16, State 101, Line 36
Unable to open the physical file "C:\ReadOnlyDB\ReadOnlyDB.mdf". Operating system error 5: "5(Access is denied.)".
Msg 5181, Level 16, State 5, Line 36
Could not restart database "ReadOnlyDB". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 36
ALTER DATABASE statement failed.

The key to understanding this error lies in the message itself. It mentions an “Operating system error”. To resolve this issue, we need to detach the database and change the file attributes in the operating system. Execute the following commands:

USE [master]
GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'ReadOnlyDB'
GO

Next, navigate to the file system and check the attributes of the database and log files. In our example, the MDF file had the “Read-only” attribute enabled. Uncheck this attribute for the file. Once done, reattach the database using the following command:

USE [master]
GO
CREATE DATABASE [ReadOnlyDB] ON (FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB.mdf'), (FILENAME = N'C:\ReadOnlyDB\ReadOnlyDB_log.ldf') FOR ATTACH
GO

Now, the database should be back to normal and you should be able to work with it as a read-write database.

We hope this article has helped you understand the reasons behind read-only database issues in SQL Server and provided you with the necessary steps to resolve them. Remember, learning is a continuous process, and encountering and solving such issues can enhance your SQL Server skills. If you have any questions or if you have encountered similar situations in the past, feel free to share your experiences with us.

Happy coding!