43

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
Maanu
  • 5,093
  • 11
  • 59
  • 82

15 Answers15

91

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.

enter image description here

Varun Rathore
  • 7,730
  • 3
  • 27
  • 30
  • 8
    In addition to the steps above, I also had to give the *Network Service* account full control of the MDF and LDF files. – DougCouto Apr 22 '16 at 17:39
  • While this shows how to change the mode, it glosses over the real problem, which is SQLServer service doesn't have read/write permissions to the File or Folder where the mdf/ldf are. I had to use Windows Explorer to Properties/Security to grant Write access. See the other answer which goes into more detail. – ripvlan Mar 10 '23 at 18:25
22

Giving the sql service account 'NT SERVICE\MSSQLSERVER' "Full Control" 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

  1. Right click the database (mdf/ldf) file or folder and select "Properties".
  2. Select "Security" tab and click the "Edit" button.
  3. Click the "Add" button.
  4. Enter the object name to select as 'NT SERVICE\MSSQLSERVER' and click "Check Names" button.
  5. Select the MSSQLSERVER (RDN) and click the "OK" button twice.
  6. Give this service account "Full control" to the file or folder.
  7. Back in SSMS, right click the database and select "Properties".
  8. Under "Options", scroll down to the "State" section and change "Database Read-Only" from "True" to "False".
Community
  • 1
  • 1
SimplyInk
  • 5,832
  • 1
  • 18
  • 27
  • Worked like a charm for me. – Hutch Mar 17 '16 at 18:50
  • That's great! Fantastic! – SimplyInk Mar 18 '16 at 02:25
  • This was the root of my problem. A new volume was added to the server, but the folder permissions were not applied. Hence all reattached dbs were read-only. After changing permissions, I could successfully ALTER DATABASE SET READ_WRITE. – ripvlan Mar 10 '23 at 18:22
  • For me, the object name NT SERVICE\MESSQLSERVER at step 4 cannot be found after clicking "Check Names". What other name can be tried? – Denis G. Labrecque Jul 28 '23 at 16:43
  • @DenisG.Labrecque https://dba.stackexchange.com/a/318765 The account isn't always named NT Service\MSSQL, if you have a named instance it is named NT Service\MSSQL$. You can list all service accounts to verify which ones you have by running get-service | foreach {Write-Host NT Service\$($_.Name)} in powershell. – SimplyInk Aug 25 '23 at 02:23
  • easy pesy solution – TPG Aug 26 '23 at 02:25
18
ALTER DATABASE [DatabaseName] SET READ_WRITE
jbr
  • 6,198
  • 3
  • 30
  • 42
George Matuthu
  • 181
  • 1
  • 2
17

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.

Xavier Poinas
  • 19,377
  • 14
  • 63
  • 95
  • 5
    +1 for the hint about `Program Files` - SQL Server data files really don't belong into the `Program Files` directory!! – marc_s May 19 '11 at 07:16
  • When I copied the .mdf and .ldf file to the sql server installation folder, It creates a read-write database. what would be the problem? – Maanu May 19 '11 at 07:25
  • The problem is that the Program Files directory is read-only. – Xavier Poinas May 19 '11 at 07:34
  • I placed them in a newly created folder. But the database created is still a read only database. – Maanu May 19 '11 at 09:33
  • Is that new folder a subdirectory of Program Files? – Xavier Poinas May 19 '11 at 22:49
  • Youll probably find the database folder and files are set to read only on your local machine. Try making them not read only. It's impossible. This is apparently a known bug but which isnt really being addressed by MS. – Phill Healey Mar 03 '14 at 10:08
  • I had to add Full-Control permission to the Users group even though I was an admin. – paul-2011 Feb 03 '16 at 17:12
15

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...

Gilad Gat
  • 1,468
  • 2
  • 14
  • 19
8

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.

Patman
  • 121
  • 1
  • 4
4

Another Way which worked for me is:

After dettaching before you attach

  • -> go to the .mdf file -> right click & 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

Xongo
  • 41
  • 3
3

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.

Arafat
  • 1,390
  • 2
  • 12
  • 18
1

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.

Dave Jarvis
  • 30,436
  • 41
  • 178
  • 315
aSterX
  • 106
  • 2
1

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.

Francis Rodgers
  • 4,565
  • 8
  • 46
  • 65
1

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

Aileen Gregg
  • 23
  • 3
0

Here is what worked for me (OS 2016/SQL 2016):

  1. First I ensured that the source and destination SQL had the same version/build.

  2. Log into the server as an administrator:

a. Make sure that the SQL Service account has read, write, execute permissions on the files you want to attach. For me, I noticed that permission-inheritance was disabled, so I re-enabled inheritance and ensured the service account had the correct permissions.

b. Open SSMS on the server and re-run your CREATE DATABASE statement.

webwilliam
  • 1
  • 1
0

just open new query and write this:

alter database [your database] set read_write
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Hossein Pazouki
  • 1
0

The procedure is simple to avoid locking db with read-only atribute:

  1. Detach database
  2. Move the files mdf and ldf to the new location on your computer
  3. Right click on mdf and ldf files (in turn, for each file), Properties, Security Tab, for the ComputerName User check full control. Apply, OK.
  4. Attach database from your new location you put the files
Florin Savin
  • 1
-1

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://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions#VA_Desc for details configuring service permissions

Luca Arpaia
  • 46
  • 4