READ_ONLY/READ_WRITE - Database Update Options in SQL Server

Q

How to set database to be READ_ONLY in SQL Server?

✍: FYIcenter.com

A

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.

 

Managing Databases and Physical Files in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-20, 323👍, 0💬