August 3, 2006 at 6:02 am
Hello
because of legacy code our app needs to attached a database before it wants to work with it and detaches it when its finished.
Unfortunally databases can only be attached read only when doing it by code. The SQL is:
EXEC sp_attach_db [db_115406169640] ,'D:\Database files\db_data.mdf','D:\Database files\db_log.ldf'
Orginal, these files have been Access databases and have been converted by a tool made by us. Did we overlooked something while doing so?
The files them self are not read only.
Do you know why the databases can only be attached read only???
Thanks
Matt
August 3, 2006 at 6:32 am
Matt,
attaching a datbase doesn't change settings like read_only or Single_user, no matter if you do it through the GUI or through sp_attachDb.
Your database must be in read_only mode. What happens if you attach the database and when run :
ALTER DATABASE YourDB SET READ_WRITE
Markus
[font="Verdana"]Markus Bohse[/font]
August 3, 2006 at 8:07 am
Hi
I'll get the error:
Unable to open physical file "..._data.mdf". Operating system error 5: 5(Access Denied)
Also for the ldf file.
But that does not make sense because the database was only attached and single user or read only have not been set. Also the File is on a local drive and not read only...
Does the login maybe needs a special privilege ?
Thanks
Matt
August 3, 2006 at 9:45 am
Matt,
when do you get this error ? While attaching the database, then probably the path you supplied in the is not correct. Also make sure that the user executing sp_attach has NTFS access to the folder there the mdf and ldf files are.
Markus
[font="Verdana"]Markus Bohse[/font]
August 3, 2006 at 9:58 am
No
(sorry that i wasnt clear, i am only going nuts about this... :-/ )
Attaching works fine. But unfortunally these db´s are attached read only. When i try to change something (like ALTER DATABASE YourDB SET READ_WRITE) i get this error.
The user we (i) created has the roles dbcreator, diskadmin, bulkadmin.
Also, when i login with this user using the Management studio it works fine. But doing it by code using the attach-sp (see first post) makes the db read only...
We are using java with jTDS-JDBC. The con string is "jdbc:jtds:sqlserver://IBMPC065;instance=SQLEXPRESS;Integrated Security=SSPI"
The MDF and LDF file is being extracted from a zip file and copied to a temp directory. All files have read only not set.
Connection is by tcp/ip.
To be honest... i am quite lost at the moment...
Thanks
Matt
August 3, 2006 at 1:40 pm
Matt,
the error you wrote about Operating system error 5: 5(Access Denied)
has nothing to do with being read-only. I actually doubt it very much that you can read from the database.
It's an permission error on OS level. My guess is that the SQL Service account has not the right NTFS permissions to open the file. Check the permissions on the folder and the files.
Markus
[font="Verdana"]Markus Bohse[/font]
August 4, 2006 at 7:25 am
Reading i can fine. Thats not the problem. Only writing wont work...
For some reason the dir have "read only" in 3rd state (neither checked nor clear) and i cant clear it for some reason...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply