I'm working on an application which automatically attaches MDF files and connects to them in SQL Server LocalDB. I need to allow scenarios where each user has their own SQL Server LocalDB instance on the same computer, and all the instances attach to the same MDF file. I realised I could do this by making the database read-only: ALTER DATABASE MyDatabase SET READ_ONLY.
However, the database sometimes resets itself to read-write mode when the application (and I, in SSMS) connect or disconnect. This seems to be because auto-close is enabled. When I run ALTER DATABASE MyDatabase SET AUTO_CLOSE OFF, I no longer get the problem.
When I try the same in another edition of SQL Server, I don't see the same behaviour. Maybe there is something special about LocalDB. However, I do need to use LocalDB.
Is there a way to stop this odd resetting behaviour without disabling auto-close?
Are there significant drawbacks to having a large number of databases on the same computer with auto-close disabled?
