database attached in read_only

[jeanmarc78]

Hi,

Here is another of my problem:
I attach my database with a SQL script, but it appears as READ_ONLY when i do this with IS, but i want it in READ_WRITE.
It's strange because when I execute the same script in SQL server, it appears as READ_WRITE without doing anything more. I should precise that i'm connected to my server with the same login and pwd.

I already tried "ALTER DATABASE mydatabase SET READ_WRITE", it works on SQL server but not with IS ( " statement failed " ). I also can't modify the database installed with IS in READ_ONLY when i'm in sql server : "permission denied" (again: attach was done with the same account)

Does anyone know what should I do or where am I wrong?
Thanks a lot

[hidenori]

I am guessing that the database that you are trying to modify is in use. Try calling the USE master statement before the ALTER DATABASE statement, and see if it fixes the problem.

[jeanmarc78]

That works perfectly, thanks a lot !

[jeanmarc78]

Hello,

my problem is happening again but there no error at all...
there is my script to attach my db:

CREATE DATABASE cml3 ON ( FILENAME ='%DIR%database\cml3.mdf') FOR ATTACH ;
USE MASTER ;
ALTER DATABASE cml3 SET READ_WRITE;
(I set a text replacement for %DIR%)
I think i add to put permission on the db file to let all users having "full control" but i'm doing it wrong maybe... In the permission page I put "Everyone" as "user", seems to work cause I see it in security property and when i do the alter database on SQL server it works, I don't know why my script isn't applied 😕

I hope someone met this before !
thanks

[hidenori]

If you run the same script from Microsoft SQL Server Management Studio, does it work as expected? Note that you need to specify a hard-coded path for the FILENAME arguement.

[jeanmarc78]

yes, if I do the script with SQL management studio, it do it well, my database will be in RW.

I tried, with IS, to put a hard-coded path like "c:\database\cml3.mdf" in my script : my db is in Read Only. after that, if I do just "alter database cml3 set read_write" on sql server, it works (connected to sql server with the same login/pass) and my DB is in RW.

Same thing happen when I do this when the path is like "%DIR%database\cml3.mdf" , replacing %DIR% with [INSTALLDIR] (I also tried to do it in hard-coded like c:\program files\mycompany\myprog\database\cml3.mdf). I only get my DB in Read_only

Is it because of my session on XP ? Maybe I haven't got an administrator session. (that's why i'm trying to put permissions to "everyone" but i don't think it's a good idea on a DB file)

[hidenori]

Can you put GO statements as follows and see if it fixes the issue?

CREATE DATABASE cml3 ON ( FILENAME ='%DIR%database\cml3.mdf') FOR ATTACH;
GO
USE MASTER;
GO
ALTER DATABASE cml3 SET READ_WRITE;
GO

If you still see the problem, would it be possible for you to send me a sample .mdf file at hyamanishi@flexerasoftware.com so that I can look into it?

[jeanmarc78]

With the GO statement, the ALTER DATABASE fail: "ALTER DATABASE statement failed (5069)" and the DB is attached in READ_ONLY.
I sent you a mail with my DB file.
Thanks !

[hidenori]

Thank you for providing your sample MDF file. I tested it on Windows XP with the steps below, but it looks like that the database was attached as READ_WRITE. Please let me know if I am missing something.

[LIST=1]

Install Microsoft SQL Server 2005 Express SP1.

Copy cml3.mdf to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Launch InstallShield 2010 IDE.

Create a connection in the SQL Scripts view.

Uncheck the "Create Catalog If Absent" checkbox

Add a new SQL script with the following script:
CREATE DATABASE cml3 ON ( FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\cml3.mdf') FOR ATTACH;
GO

Build and run the setup.

On the SQLLogin dialog, specify (local)\SQLEXPRESS for the target server, and select the Windows authentication credentials of current user radio button, and then click the Next button.

Complete the setup.

[jeanmarc78]

It seems that it works when i select the windows authentification, the database will be in read_write, thanks a lot Hidenori .

Do you know why i can't do this with the sql auth ? Is it because I use a wrong SQL account? Cause i need to get the SQL login and password to configure a connection string in a web.config, and I don't know how will i do if i let the user choose between windows and SQL auth.

[hidenori]

If you login as the 'sa' account, is the problem reproducible? The database was attached in READ_WRITE in that case as well on my side.

[jeanmarc78]

That's really strange, i always use the 'sa' login and it's always in read_only :confused:

I just saw something: when I use 'sa' and the file name is 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\cml3.mdf' (hard-coded, like you said), the db is in read_write. I'm afraid it won't help me 😞

I really don't understand something: SQL server can't create log files anywhere, for now I just saw it create them in the path you gave me, or Inetpub\database.
another thing:
Logged with 'sa', I attach 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\cml3.mdf', it works well and it's in read_write.
I detach it and copy paste on another folder like 'c:\database\cml3.mdf', it works but it's read_only.
After that, i detach it, and delete the LDF in 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data', then if i try to attach 'c:\database\cml3.mdf' there is :
"File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\cml3_log.LDF" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Niveau 16, État 2, Ligne 1
Could not open new database 'cml3'. CREATE DATABASE is aborted."

[hidenori]

Yes, it's really strange. I receive the following error as you do when I log in using the SQL authentication and try to attach the database file in a different location from 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data':

File activation failure. The physical file name "c:\database\cml3_log.LDF" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'cml3'. CREATE DATABASE is aborted.

I'm guessing that it's a limitation or issue of the Express edition of Microsoft SQL Server 2005 because I'm able to attach the database in read-write successfully in the Enterprise edition in any case. You may want to consult with Microsoft to find out if there is any workaround.

[jeanmarc78]

Hello,

Finally, we found out the explication ! this is about permissions on folders...
I set it with "everyone" but it works for users only and we needed "network service", that's all...
Actually, my boss found this in 5 minutes (I'm in an internship)... a big FAIL for me ^^

Thanks Hidenori, you taught me a lot anyway.