SQL SERVER – Making Database to Read Only – Changing Database to Read/Write

I recently received the following comments on my earlier blog about Making database to read only.

“Today I was trying to attach the (MDF, NDF, LDF) SQL server 2008 database which i have received from my client. After attachment the database status is showing (Read-Only) (Eg.database name (Read-Only). How do I make to normal mode for the data updating. Is there any query available to resolve this problem. Your help will be highly helpful.”

Let’s learn Making Database to Read Only and Changing Database to Read/Write with the help of T-SQL scripts.

Here is the answer:

Make Database Read Only

USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT
GO

Make Database Read/Write

USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT
GO

If you face an error that if the database is already in use, you can resolve the same by making database in single user mode – here is the guideline SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE.

Let me know if you have ever faced this situation in your business or life by leaving note in the comment section in the blog. I read every single comment.

Why an Attached Database Shows Read Only and What to Check

In the reader’s case, the database did not choose to be read-only. When you attach files that came from another machine, SQL Server may open the database as read-only because it cannot write to those files. Usually the files are marked read-only in Windows, or the SQL Server service account can read them but not write to them. The ALTER DATABASE command alone will not fix that.

Here is the order I would follow:

• Check the current state with SELECT name, is_read_only FROM sys.databases;
• Open the properties of the .mdf, .ndf and .ldf files in Windows and clear the read-only attribute.
• Give the SQL Server service account full control on the folder that holds the files.
• Run the READ_WRITE script from above, and then test with a small update.

One more tip: WITH NO_WAIT makes the command fail right away if other sessions are using the database. If you are sure it is safe to disconnect them, WITH ROLLBACK IMMEDIATE rolls back their open work and lets the change go through.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.