SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Database is ReadOnly when I attach it...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

malachi151
Posting Yak Master

148 Posts

Posted - 10/12/2007 :  11:28:16  Show Profile  Visit malachi151's Homepage  Reply with Quote
Using SQL Express in this case, I have a database on one machine using one instance of SQLExpress that I detach. I then copy the database to a USB drive and take it to a different machine and attach it to a different SQL Express instance and it comes in as ReadOnly and I know of no way to change it.

One difference is that I have different users on the two systems, is that the cause? I wouldn't think that this would cause the database to show as ReadOnly, thoughI would just think that I may not be able to access it aside from using sa.

What is the likely cause of this?

Thanks

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/12/2007 :  11:32:11  Show Profile  Reply with Quote
You sure it wasn't read-only when you detatched it?

ALTER DATABASE MyDatabaseName SET READ_WRITE

Add WITH ROLLBACK IMMEDIATE if the DB is in use and its doesn't let you.

Kristen
Go to Top of Page

malachi151
Posting Yak Master

148 Posts

Posted - 10/12/2007 :  11:51:51  Show Profile  Visit malachi151's Homepage  Reply with Quote
Yes, I'm sure, and to verify I just reattached it from the USB drive to the origional SQLExpress instance and it comes in just fine. When I attach it to the other instance it comes in ReadOnly.

I guess I should try backup and restore instead?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/12/2007 :  13:06:36  Show Profile  Reply with Quote
I'd try setting it to READ_WRITE first

Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 10/12/2007 :  13:33:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
It probably has to do with the USB drive. Perhaps the file is set to read-only on the USB drive and thus read-only when you copy to the server, and then perhaps SQL Server sees this file flag and then sets the database to that setting too.

I've never done it this way, so there's a lot of "perhaps" here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

malachi151
Posting Yak Master

148 Posts

Posted - 10/12/2007 :  15:31:32  Show Profile  Visit malachi151's Homepage  Reply with Quote
I checked that also, in fact it was the first thing I checked. Nope, the Read-Only flag is not checked on the file itself, and I also copied the file from the USB drive back to the drive on the source comptuer and re-attached it and it worked fine....

I'll try setting it to READ_WRITE, but I suspect that this won't work because I already tried that via the Properties UI and that didn't work. Still sometimes thing work via script that don't work via UI, so I'll try it via script.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/12/2007 :  17:58:59  Show Profile  Reply with Quote
"I suspect that this won't work because I already tried that via the Properties UI and that didn't work."

They are going to do the same job, so I'll put my money with your hunch!

However, this is NOT normal behaviour.

A restored database takes on all then characteristics of the database it was backed up from. If it was Read/Write when it was backed up that's what it will be after restore (or attach).

Is it possible that SQL Server has only READ ONLY permissions on the file? (e.g. the folder it has been copied into has different security profiles to the source machine?)

Kristen
Go to Top of Page

malachi151
Posting Yak Master

148 Posts

Posted - 10/12/2007 :  19:47:54  Show Profile  Visit malachi151's Homepage  Reply with Quote
Well, for whatever reason, doing a backup and restore worked fine, but simply reattaching the mdf still didn't work.

It seems to me that I should have been able to simply detach the database, make a copy of it, and then attach the copy, but that didn't work.

Making a backup and then copying the backup and restoring the backup on the new server worked however.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37134 Posts

Posted - 10/12/2007 :  21:35:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
That is very interesting. I wonder if there are any limitations in Express with regards to detach/attach. I only use Enterprise Edition of SQL Server, so I'm not familiar with the different limitations of the lower edition products.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/13/2007 :  10:22:57  Show Profile  Reply with Quote
Doing a "restore" is creating a new file (or using an existing one) on the target machine, as such it will inherently have appropriate file level permissions - even if, for whatever reason, the Backup File being restored from only has Read permissions.

If you have a moment might be worth checking what the security permissions were on the MDF file you copied across. I reckon SQL Server will have only had Read permission [not the same as the file being Read-Only, but if the application attempted to Write to the file that would have been denied, which would look like it was Read Only I suppose]

Kristen
Go to Top of Page

malachi151
Posting Yak Master

148 Posts

Posted - 10/13/2007 :  19:14:28  Show Profile  Visit malachi151's Homepage  Reply with Quote
That's not it. As I said, I can detach it and attach it as much as I want on the same server, it comes in normal every time. When I copy it to the other server and attach it, then it comes in Read Only. The database image in the SSMSEE tree is actually gray and the database says (Read Only) next to the name in the tree.

I'm not going to fool with it anymore. I don't know what the issue is, but doing a backup and restore does what I need in this case.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 10/14/2007 :  01:04:12  Show Profile  Reply with Quote
"I don't know what the issue is, but doing a backup and restore does what I need in this case."

Yup, agree with that, I was just trying to get to the bottom of it for the next unfortunate soul who Google's this!

"When I copy it to the other server and attach it, then it comes in Read Only."

Not sure I explained what I meant clearly.

When you copy that file it will take the file permissions [from the Source machine] with it. On the Target Server those security setting may not provide WRITE permission to the "windows logon" that the Target server's SQL Service is using when it tries to Attach the file

Anyways, you've got a working solution, and my preference would be for Backup / Restore anyway.

Kristen

Edited by - Kristen on 10/14/2007 01:04:48
Go to Top of Page

josjorge
Starting Member

Costa Rica
2 Posts

Posted - 06/29/2008 :  09:56:55  Show Profile  Reply with Quote
Hi,

I've got the same problem when i detached a database from my SQL at home and tried to attach it to my office SQL. I fixed it doing this.
In the admin tools (Control Panel)go to services , open the one for SQL Server(SQLEXPRESS) ,go to log on tab ,change the login credentials to Local System Account, restart the service and then try to attach the database again. It worked for me

Jose


quote:
Originally posted by malachi151

Using SQL Express in this case, I have a database on one machine using one instance of SQLExpress that I detach. I then copy the database to a USB drive and take it to a different machine and attach it to a different SQL Express instance and it comes in as ReadOnly and I know of no way to change it.

One difference is that I have different users on the two systems, is that the cause? I wouldn't think that this would cause the database to show as ReadOnly, thoughI would just think that I may not be able to access it aside from using sa.

What is the likely cause of this?

Thanks

Go to Top of Page

eagle
Starting Member

1 Posts

Posted - 11/26/2008 :  04:16:49  Show Profile  Click to see eagle's MSN Messenger address  Reply with Quote
I had the same problem when I first did a silent installation with sqlexpr.exe, and did not specify sqlaccount="NT AUTHORITY\SYSTEM", on a Windows XP SP 2 and SP 3. The result was the database I "silently" attached, became a read-only, contrary to what I needed. The default was Network Service.

After installation of the SQL Express, I changed to Local System. Then by executing "alter database mydbname set read_write", it became read-write access, thus solving my read-only problem.
Go to Top of Page

ibeckett
Starting Member

USA
12 Posts

Posted - 11/18/2009 :  11:49:06  Show Profile  Visit ibeckett's Homepage  Click to see ibeckett's MSN Messenger address  Reply with Quote
Hey I know this is an old thread, but I had a similar issue that turned this up.

After copying the DB's from an USB/eSATA drive to a local hard drive, we had the same problem as the thread starter - where the DB's were attaching as read only.

The fix was quick and simple: Go to the DB file on the local hard drive and fix the permissions so that the local admin had full access on the file.

Thanks,

Ian Beckett
ibeckett at gmail dot com
www.sqlblog.ibeckett.com
Go to Top of Page

ITM_2005
Starting Member

1 Posts

Posted - 09/30/2010 :  05:02:25  Show Profile  Reply with Quote
This worked perfectly for me.

quote:
Originally posted by josjorge

Hi,

I've got the same problem when i detached a database from my SQL at home and tried to attach it to my office SQL. I fixed it doing this.
In the admin tools (Control Panel)go to services , open the one for SQL Server(SQLEXPRESS) ,go to log on tab ,change the login credentials to Local System Account, restart the service and then try to attach the database again. It worked for me

Jose


quote:
Originally posted by malachi151

Using SQL Express in this case, I have a database on one machine using one instance of SQLExpress that I detach. I then copy the database to a USB drive and take it to a different machine and attach it to a different SQL Express instance and it comes in as ReadOnly and I know of no way to change it.

One difference is that I have different users on the two systems, is that the cause? I wouldn't think that this would cause the database to show as ReadOnly, thoughI would just think that I may not be able to access it aside from using sa.

What is the likely cause of this?

Thanks



Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 10/26/2010 :  12:14:47  Show Profile  Reply with Quote
Adding my experienced to this thread. I had this same issue also. On the same server I detached the database, moved the data file to a different drive, and reattached. It came back in read only mode. I executed an alter database to set it to read_write and the operation went into a spinloop. The status would sometimes change to running for a few seconds but it was stuck in a spinloop. The sql server generated a stack dump and was putting non-yielding scheduler warnings in the error log. When i tried to kill the connection it then was stuck in killed/rollback status. The connection then started to block some of the other system SPIDs, which in turn started blocking other user connections to different databases. I had to do an emergency restart of the service, but when it would start back up it would immediatly stack dump again when it tried to run recovery to this database that i just attached and was stuck in read only. I started the server with the trace flag to skip the recovery phase for all databases, hoping i could then just drop the bad database, and restart it normally, but when I executed the drop database it stack dumped again. At this point I needed to move quick. I simply deleted the data file for the bad database from the disk, and started the server. Everything game up fine. The bad database was still in read only mode, even though its data files were completely gone. I was finally able to drop the database successfully and then restore it from backup. This was a very critical outage for my company. Fortunately i was able to minimize the outage to only a few minutes.

As to the cause, I do not know for sure if it was file system permissions. I granted the local sql server group full access to the folder on this new drive that was holding the data file. The local admin group should have had access because they have full control over the root drive. I did have to change the owner of the file from the service account we are running sql server as to the local admin group to move it out of its old spot. This may have been the cause of it getting attached in read-only mode. After doing the restore, since sql server itself created the file, the account we have running the service is the owner of the file currently.

Moral of the story. Be extremely careful with file system permissions when using detach/attach to move a data file around.

- Eric
Go to Top of Page

mount77
Starting Member

1 Posts

Posted - 04/14/2011 :  18:04:09  Show Profile  Reply with Quote
After attaching a database that I copied from another machine I, too, was confronted with a Read-Only database. I received an Access Denied error on the mdf file when I tried to set the Database Read-Only property to False. To solve the problem I granted Full Control on both the mdf and log files to the account under which the SQL Server service runs. Then I could set the property to False.
Go to Top of Page

rgonv
Starting Member

Colombia
1 Posts

Posted - 01/11/2012 :  18:44:05  Show Profile  Reply with Quote
I had a similar problem, and it seems related to file level permissions. When I attached a db using one particular admin user, the db worked ok,but using a different (also administrative) login, went into readonly mode. Managed to explicitly asign full file level permissions to the second login and worked perfectly.

Hope it helps.

Cheers,

http://about.me/ricardo.gonzalez
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000