MS SQL: Database attaches as read-only and ALTER DATABASE statement fails.


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

——————————
ADDITIONAL INFORMATION:

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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=5105&LinkId=20476

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s