Forum Discussion
Why is my database set to read only and how can I fix it?
Javier_Villegas Finally found out it was likely because I was running SQL Server in a Linux container. It said in https://www.nocentino.com/posts/2021-09-25-container-file-permissions-and-sql/ that the user accounts and groups on the base OS likely don’t sync up with the user accounts and groups inside the container.
Anyway all I did is use this command to list the log and mdf file inside the container:
docker exec MyContainer bash -c 'ls -lan /var/opt/mssql/data/MyDatabase*'
This was the output. The permissions show it was read-only in the container.
-rw-r----- 1 501 20 21474836480 Sep 24 14:23 MyDatabase.mdf
-rw-r----- 1 501 20 1073741824 Sep 24 14:23 MyDatabase_log.ldfI fixed that by running these 2 commands which allowed me to finally write to the database:
docker exec -u 0 MyContainer bash -c 'chown 10001:0 /var/opt/mssql/data/MyDatabase*'
docker exec -u 0 MyContainer bash -c 'chmod 660 /var/opt/mssql/data/MyDatabase*'
Seems like the database files are in read-only mode so sql get the DB as read-only
I don't know the specifics for Linux but in the Windows Server world this could be that the SQL Service account does not have the proper rights on the disk and/or folder where the data or logs files are located.
Regards
Javier
Javier_Villegas Finally found out it was likely because I was running SQL Server in a Linux container. It said in https://www.nocentino.com/posts/2021-09-25-container-file-permissions-and-sql/ that the user accounts and groups on the base OS likely don’t sync up with the user accounts and groups inside the container.
Anyway all I did is use this command to list the log and mdf file inside the container:
docker exec MyContainer bash -c 'ls -lan /var/opt/mssql/data/MyDatabase*'
This was the output. The permissions show it was read-only in the container.
-rw-r----- 1 501 20 21474836480 Sep 24 14:23 MyDatabase.mdf
-rw-r----- 1 501 20 1073741824 Sep 24 14:23 MyDatabase_log.ldf
I fixed that by running these 2 commands which allowed me to finally write to the database:
docker exec -u 0 MyContainer bash -c 'chown 10001:0 /var/opt/mssql/data/MyDatabase*'
docker exec -u 0 MyContainer bash -c 'chmod 660 /var/opt/mssql/data/MyDatabase*'