[sql-server] database attached is read only

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

This question is related to sql-server sql-server-2005

The answer is

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.

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

Right click the database (mdf/ldf) file or folder and select "Properties".
Select "Security" tab and click the "Edit" button.
Click the "Add" button.
Enter the object name to select as 'NT SERVICE\MSSQLSERVER' and click "Check Names" button.
Select the MSSQLSERVER (RDN) and click the "OK" button twice.
Give this service account "Full control" to the file or folder.
Back in SSMS, right click the database and select "Properties".
Under "Options", scroll down to the "State" section and change "Database Read-Only" from "True" to "False".

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

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.

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

ALTER DATABASE [DatabaseName] SET READ_WRITE

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.

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

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

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.

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