I just reinstalled my MS SQL 2000 and I was reattaching my databases.
The first one I tried, attached as read-only. That seemed already suspect. However, I thought it is just a default way of setting it in some situations upon attaching and it just happened to be in that situation. Never mind, right click on the database, properties, options, remove the checkmark for the Read-only setting and apply, will fix it, I believed. Wrong! When I tried to apply it I immediately received:
TITLE: Microsoft SQL Server Management Studio Express
Alter failed for Database ‘database1’. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
Device activation error. The physical file name ‘E:\SQL_Storage\database1.mdf’ may be incorrect.
Device activation error. The physical file name ‘E:\SQL_Storage\database1_log.ldf’ may be incorrect.
Could not restart database ‘database1’. Reverting back to old status.
ALTER DATABASE statement failed.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Device activation error. The physical file name ‘E:\SQL_Storage\database1_log.ldf’ may be incorrect. (Microsoft SQL Server, Error: 5105)
I have searched a lot on the Internet but none of the articles helped me. That’s because my problem was not being caused by missing log files or corrupted data files or … many other issues that were being described but rather a NTFS permissions problem.
When I installed the new server I set it up to run under a regular user account "sqlserver" – different from what I was using before. I realized that this account didn’t have any kind of permissions on the data file. As soon as I gave the "sqlserver" account permissions ("full permissions" mind you) the database reattached nicely without the dreaded read-only flag and I have all the control in altering its characteristics.
Hope this will help.