locked
Detach, Move & Attach Database RRS feed

  • Question

  • Hi,

    I am doing the following task on SQL Server.

    1 - Detach Database

    2 - Move MDF file to different drive

    3 - Attach Database 

    But after doing this i see the Database on SQL Mgmt studio shows (Read Only).

    Please suggest how to fix this.

    Regards,

    Maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Tuesday, June 12, 2012 5:31 AM

Answers

  • I tried the option earlier property --> option --> state --> db read only =  False but  it didnt work it was giving me permission error.

    I have given everyone group full control on the mdf file which i moved and then i see that i have full access to Database.

    Is that the right way to fix this issue?

    not really - only the SQL Server service account need access to the database files (.MDF, .NDF, .LDF etc.) and this is mainly solved by giving the correct sql server local windows group access to the directory and not directly to the file and not directly to the SQL Server service account.

    Whenever the SQL Server service account is changed by SQL Server Configuration Manager tool - the only right way to change it - the new service account is added to the appropriate SQL Server Windows group which had been created during the installation of SQL Server with the correct set of permission required to run the SQL Server.

    How to found the right permission: I would just copy the permission assigned to the SQL default database location and set them on the new directory.

    If you don't care about everybody having direct access to the database you can leave it as it is now but that is definitively not recommanded for several reasons and from your signature I assume you do know why.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Tuesday, June 12, 2012 3:47 PM

All replies

  • try to run this see if it works:

    USE [master]
    GO
    ALTER DATABASE [Your_db_name] SET  READ_WRITE WITH rollback immediate;
    GO

    And check to see if your SQL service account has write permission to the drive and folder you move to!!


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Tuesday, June 12, 2012 6:25 AM
  • Hi

    You have to chainge the below option

    property --> option --> state --> db read only =  False

    after that it works.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Tuesday, June 12, 2012 6:41 AM
  • Hi,

    I tried the option earlier property --> option --> state --> db read only =  False but  it didnt work it was giving me permission error.

    I have given everyone group full control on the mdf file which i moved and then i see that i have full access to Database.

    Is that the right way to fix this issue? 

    Regards,

    Maqsood


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Tuesday, June 12, 2012 9:53 AM
  • I tried the option earlier property --> option --> state --> db read only =  False but  it didnt work it was giving me permission error.

    I have given everyone group full control on the mdf file which i moved and then i see that i have full access to Database.

    Is that the right way to fix this issue?

    not really - only the SQL Server service account need access to the database files (.MDF, .NDF, .LDF etc.) and this is mainly solved by giving the correct sql server local windows group access to the directory and not directly to the file and not directly to the SQL Server service account.

    Whenever the SQL Server service account is changed by SQL Server Configuration Manager tool - the only right way to change it - the new service account is added to the appropriate SQL Server Windows group which had been created during the installation of SQL Server with the correct set of permission required to run the SQL Server.

    How to found the right permission: I would just copy the permission assigned to the SQL default database location and set them on the new directory.

    If you don't care about everybody having direct access to the database you can leave it as it is now but that is definitively not recommanded for several reasons and from your signature I assume you do know why.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Tuesday, June 12, 2012 3:47 PM