Post reply

Read-Only after Attach

  • May 5, 2010 at 1:05 pm

    #138908

    Hello Everyone

    I just finished attaching a couple databases, and they are appearing in SSMS as read-only.

    What may be causing this? How can I change them from their current "read-only" state?

    I have done this same thing after many server rebuilds, and this is the first time the databases are in the read-only state.

    Thanks

    Andrew SQLDBA

  • May 5, 2010 at 1:21 pm

    #1162723

    ALTER DATABASE DBNAME

    SET MULTI_USER;

  • May 5, 2010 at 1:45 pm

    #1162751

    Is the mdf readonly? Is the database file in an encrypted or compressed folder? Does the SQL service account have full security permissions on that folder and on the files?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 5, 2010 at 1:46 pm

    #1162753

    adalberto-339588 (5/5/2010)

    ALTER DATABASE DBNAME

    SET MULTI_USER;

    That will change a database from single user or restricted user to multi user. If will not change a database from read-only to read-write.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 5, 2010 at 2:59 pm

    #1162814

    Hello Everyone

    They are still read-only. All the other databases are under the same parent directory. And they are doing fine. These are the only two that are read-only. This is rather odd.

    Is there anything else that I can look at or try?

    Thanks

    Andrew

  • May 5, 2010 at 3:21 pm

    #1162832

    Try

    ALTER DATABASE dbname

    SET READ_WRITE;

    Leo

    Is it Friday yet?

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • May 5, 2010 at 3:52 pm

    #1162852

    If the ALTER doesn't work, what error does it give?

    Are the files read-write?

    OS?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 5, 2010 at 4:13 pm

    #1162860

    Note that

    ALTER DATABASE DBNAME

    SET MULTI_USER;

    Will "work" on a database in READ_ONLY mode, i.e. it doesn't return an error, but it also doesn't take the database out of READ_ONLY mode. The statements change different bits in the sys.databases.status column, so you can have a database in READ_ONLY and change it between MULTI_USER and SINGLE_USER, but it will stay READ_ONLY.

    Leo

    Is it Friday yet?

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply