Originally the app only worked with SQL Server 2000 (or MSDE), but after SQL Server 2005 (SQL Express) had been available for a while, we decided to support it too. It turned out to be harder than we expected, because every time we detached a database, we were unable to attach it later.
The errors looked like this:
Unable to open the physical file "C:\MyDatabase.mdf". Operating system error 5: "5(Access is denied.)"
Turns out that SQL Server 2005, in order to better secure the data inside an MDF file, automatically resets file permissions whenever the MDF is detached. I got no problem with that -- it makes sense, when you think about how before, your data was always securable as long as you were accessing it through the SQL Server engine, but if you forgot to secure your MDFs and LDFs, anyone who got their hands on those could probably get whatever information they wanted out of them.
Just, for our app, it made life crazy. Permissions would get reset so that only the person who did the detach could re-attach. Or sometimes, a process running as a service under the Local System account would detach, and then nobody could manually re-attach.
Luckily, after a lot of research that took way too long, we discovered trace flag 1802. Trace flag 1802 turns off the permission-resetting behavior. This particular trace flag needs to be set in the startup properties for the instance, so that it is always in effect. If you set it dynamically using DBCC TRACEON, it won't stick through the next time you restart your instance.
To enable trace flag 1802:
1. Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager
2. In the tree list on the left, click on SQL Server 2005 Services
3. On the right, right-click your SQL Server instance name, and choose Properties
4. In the Properties dialog, click on the Advanced tab
5. Find the property called Startup Parameters. There is a long string of parameters in this field. Click the dropdown arrow to view and edit the string.
6. At the end of the string, add ;-T1802