database attached is read only

Question

I used the following the script to attach a database  But the database created is read only  What modifications should I make in the script to make it read-write  Please help me    USE  master  GO CREATE DATABASE  GemDatabase  ON    FILENAME   N E  Program Files  x86  ICE Products ICEConnect200 New Database GemDatabase mdf       FILENAME   N E  Program Files  x86  ICE Products ICEConnect200 New Database GemDatabase log ldf     FOR ATTACH GO if not exists  select name from master sys databases sd where name   N GemDatabase  and SUSER SNAME sd owner sid    SUSER SNAME     EXEC  GemDatabase  dbo sp changedbowner  loginame N sa    map false GO

User · Answer

You need to change permission for your database folder  properties -  security tab -  edit    -  add    -  username  NT Service MSSQL SQLEXPRESS  or  NT Service MSSQLSERVER   Close the windows  open Advanced     double click the user and set as follows  Type  Allow Applies to  This folder  subfolder and files Basic permissions  all Make sure the owner is set too

User · Answer

Another Way which worked for me is   After dettaching before you attach    -  go to the  mdf file  -  right click  amp  select properties on the file -  security tab -  Check Group or usernames   for your name account  optional   and for  NT SERVICE MSSQLSERVER  NB    List item  -  if not there than click on edit button    -  click on add button    and enter search NT SERVICE MSSQLSERVER    -  click on OK -  give full rights -  apply then ok  then ok again do this for  ldf file too    then attach

User · Answer

There are 3  at least  parts to this   Part 1  As everyone else suggested   Ensure the folder and containing files are not read only  You will read about a phantom bug in windows where you remove read only from folders and containing items  only to open the properties again and see it still clicked  This is not a bug  Honestly  its a feature  You see back in the early days  The System and Read Only attributes had specific meanings  Now that windows has evolved and uses a different file system these attributes no longer make sense on folders  So they have been  repurposed  as a marker for the OS to identify folders that have special meaning or customisations  and as such contain the desktop ini file   Folders such as those containing fonts or special icons and customisations etc  So even though this attribute is still turned on  it doesn t affect the files within them  So it can be ignored once you have turned it off the first time   Part 2  Again  as others have suggested  right click the database  and properties  find options  ensure that the read only property is set to false  You generally wont be able to change this manually anyway unless you are lucky  But before you go searching for magic commands  sql or powershell   take a look at part 3   Part 3  Check the permissions on the folder  Ensure your SQL Server user has full access to it  In most cases this user for a default installation is either MSSQLSERVER or MSSQLEXPRESS with  NT Service  prefixed  You ll find them in the security logins section of the database  Open the properties of the folder  go to the security tab  and add that user to the list   In all 3 cases you may  or may not  have to detach and reattach to see the read only status removed   If I find a situation where these 3 solutions don t work for me  and I find another alternative  I will add it here in time  Hope this helps

User · Answer

Giving the sql service account  NT SERVICE MSSQLSERVER   quot Full Control quot  of the database files If you have access to the server files folders you can try this solution that worked for me  SQL Server 2012 on Windows Server 2008 R2  Right click the database  mdf ldf  file or folder and select  quot Properties quot   Select  quot Security quot  tab and click the  quot Edit quot  button  Click the  quot Add quot  button  Enter the object name to select as  NT SERVICE MSSQLSERVER  and click  quot Check Names quot  button  Select the MSSQLSERVER  RDN  and click the  quot OK quot  button twice  Give this service account  quot Full control quot  to the file or folder  Back in SSMS  right click the database and select  quot Properties quot   Under  quot Options quot   scroll down to the  quot State quot  section and change  quot Database Read-Only quot  from  quot True quot  to  quot False quot

User · Answer

First make sure that the folder in which your  mdf file resides is not read only  If it is  un-check that option and make sure it reflects to folders and files within that folder   Once that is done  Open Management Studio  in the Object Explorer right click on the Database which is read only and select Properties  In the Options Menu  check that the Read-Only property is false

User · Answer

If SQL Server Service is running as Local System  than make sure that the folder containing databases should have FULL CONTROL PERMISSION for the Local System account    This worked for me

User · Answer

ALTER DATABASE  DatabaseName  SET READ WRITE

User · Answer

You need to go to the new folder properties   security tab  and give permissions to the SQL user that has rights on the DATA folder from the SQL server installation folder

User · Answer

Answer from Varun Rathore is OK but you must consider that starting from  Windows Server 2008 R2 and higher the SQLServer service will run under a local virtual account and not anymore under the old well known  NETWORK SERVICE   Due to this  to switch a newly attached DB to  not read only mode   you must setup permissions on the ldf and mdf files for local machine user line  NT SERVICE MSSQLSERVER  where MSSQLSERVER would be the service name in a pretty standard installation   Checkout this https   docs microsoft com en-us sql database-engine configure-windows configure-windows-service-accounts-and-permissions VA Desc for details configuring service permissions

User · Answer

If you have tried all of this and still no luck  try the detach attach again

User · Answer

Make sure the files are writeable  not read-only   and that your user has write permissions on them   Also  on most recent systems  the Program Files directory is read-only  Try to place the files in another directory

User · Answer

Open database properties --  options and set Database read-only to False      Make sure you logged into the SQL Management Studio using Windows Authentication    Make sure your user has write access to the directory of the mdf and log files    Did the trick for me

[sql-server] database attached is read only

Examples related to sql-server

Examples related to sql-server-2005