Have this ever happened to you? After moving a .mdf around, you attach it with success and even make a few queries, but after some hours the database is marked as “read-only”. The SQL Server Log won’t give you a clue, then you go ahead and try to make it online fast (investigation on WHY can wait a few moments, the users can’t).
USE [master] GO ALTER DATABASE [your_base] SET READ_WRITE WITH NO_WAIT GO
But to no avail. The database stays read-only and now the message is:
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "F:\your_base.mdf". Operating system error 5: "5(Access is denied.)". Msg 945, Level 14, State 2, Line 1 Database 'your_base' 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.
Well, this is something we can use to work. Check your disk to see if the file is there and that there is enough space on it, then go ahead and check if the account used to start the SQL Service has the necessary rights on the folder/file. This usually happens when you change the SQL Service account from “Local system” to any other domain/local account and move a database file to a folder where this account doesn’t have write access.
When you try to make the database online SQL Server Log will give a better message:
FCB::Open failed: Could not open file F:\your_base.mdf for file number 1. OS error: 5(Access is denied.).
and a quick search on google will point you to the right direction (but you dont need to restart the service, just make the database read-write again or detach/attach it).