Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - READ_ONLY/READ_WRITE - Database Update Options

By: FYIcenter.com

(Continued from previous topic...)

How to set database to be READ_ONLY?

Databases in SQL Server have two update options:

• READ_WRITE - Data objects are allowed to be queried and modified. This is the default.
• READ_ONLY - Data objects are allowed to be queried, but not allowed to be modified.

You can use the "ALTER DATABASE" to change database update options as shown in the tutorial below:

USE FyiCenterComData
GO

INSERT Links (Name) VALUES ('dba.FYIcenter.com')
GO
(1 rows affected)

ALTER DATABASE FyiCentercomData SET READ_ONLY
GO

INSERT Links (Name) VALUES ('dev.FYIcenter.com')
GO
Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1
Failed to update database "FyiCenterComData" because 
the database is read-only.

SELECT * FROM Links
GO
Name
dba.FYIcenter.com

ALTER DATABASE FyiCentercomData SET READ_WRITE
GO

INSERT Links (Name) VALUES ('dev.FYIcenter.com')
GO
(1 rows affected)

As you can see from the output, inserting data into a table is not allowed if the database is in READ_ONLY mode.

(Continued on next topic...)

1. What is a database?
2. What is the simplest way to create a new database?
3. How to set the current database?
4. How to delete a database?
5. Why I am getting this error when dropping a database?
6. How to get a list all databases on the SQL server?
7. Where is my database stored on the hard disk?
8. How to create database with physical files specified?
9. How to rename databases?
10. Why I am getting this error when renaming a database?
11. What are database states?
12. How to set a database state to OFFLINE?
13. How to move database physical files?
14. How to set database to be READ_ONLY?
15. How to set database to be SINGLE_USER?
16. What are system databases?