Failed to update database because the database is read-only.

_{Tuesday, December 19, 2006 1:53 PM}

I am building a Windows Forms application in VS 2005, using C# and SQL Server 2005 Express as the backend.

When I try to accessd data from the db, it works with no problems.

When I try to insert/update/delete, I get the following error message

Failed to update database "DBNAME" because the database is read-only.

I am using a connection string with the following syntax:
"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\PATH_TO_DB\db.mdf;Database=MyDB;Integrated Security=False;User Instance=False;User ID=USR;"

I have made sure that the permissions on the DB are set (in Windows) so that all users can modify. The user that is logging in to the DB has status set to DBOWNER.

I have seen others experience the same problem - with ASP.net sites, not Windows Forms sites (thus most of their solutions dont apply to me)

Any ideas on what is wrong and how I can get this to work?

_{Tuesday, December 19, 2006 3:21 PM ✅Answered | 1 vote}

hi,

please verify the account running the SQL Server instance service has adeguate NTFS read/write permissions on the c:\PATH_TO_DB\ folder and relative db files

and verify the database is not in read-only mode

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Updateability');
GO
ALTER DATABASE AdventureWorks
    SET READ_ONLY;

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Updateability');
GO
ALTER DATABASE AdventureWorks
    SET READ_WRITE;

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Updateability');

regards

_{Tuesday, December 19, 2006 5:16 PM}

Thanks Andrea, that did the trick.

Do you know why this happened? The database in the data directory was copied there from the source directory upon building the solution within VS 2005, and the source DB was not set to read only. Why would the database have been set to read-only upon being attached? Would this have happened if the permission of the file in the data directory were not sufficient? Do you know of a way to avoid this in the future (ie: when deploying the applicsation in the real world)?

Thanks,
Yaakov

_{Tuesday, December 19, 2006 6:18 PM}

hi Yaakov,

actually I've no idea... setting a database to read-only is an explicit option and is not automatically "magically" performed..

regards

_{Wednesday, October 8, 2008 2:01 PM}

I am having the same problem, except the app is not throwing an error.

This is my connection string. I can read data, I tried the code you suggested here and I recieve  as a return value -6 and still cannot write updates, inserts etc.

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Jonny\My Documents\Visual Studio 2005\Projects\SixMonkeys\SixMonkeys\App_Code\Monkey.mdf";Integrated Security=True;User Instance=True

Couple small difrences, using VB, and VS Studio 2005

any help appreciated.

_{Wednesday, October 8, 2008 2:26 PM}

hi,

as you are dealing with a User Instance, are you sure you are inspecting, when checking your CRUD operations, the correct database? or are you sure you are not everytime overwriting the app database with your "template" database (it's property for deploy il "copy always") from your solution?

regards

_{Friday, October 10, 2008 6:08 PM}

Hi thanks for your reply

I am rather green at putting together a windows app, been working with SQl server for years as the back end for websites.

Property for deploy is "copy always".

With CRUD am using the same connection string in the app for all operations, I am assuming I am looking at the correct database.

Just developing the app. Simply setting up the database in VS, IE Solution explorer, add new Item/database. The  file is created with an .mdf extension, shows up in VS server Explorer and VS solution explorer.

In the server explorer I am able to open tables, add delete data  and all seems normal. When I write some code, to perform CRUD operations, something like a simple form or class, I can read, like fill a datagrid with some test data I put in the table from the Server explorer. I can even modify the database objects from code. (IE drop modify tables etc).

What I cant seem to do is simple CRUD operations on the data from the windows app. Cant add a record, delete a record or update a record. I will write a simple sql stored procedure to insert a new record, run it from the server explorer and it works just fine. It works just fine on a Sql server database IE mydb.obj. try it from my windows app and I can only read.

I am pretty confused about MDF files. I kind of get that it is a definition file and not actually a database, but I dont get what is going on in the black box beyond that.

_{Saturday, October 11, 2008 9:13 AM}

hi,

as already stated, I do think your problem is related to the User Instance scenario you are deling with.. you said you set the .mdf file deploy to "copy always".. this setting will always overwrite your application's used database with "your model mdf file" you have in your solution.. please have a look at Mike's blog post related to this problem..

regards

_{Saturday, January 22, 2011 8:49 AM}

raf

Thanks for sharing.