Answers

Question and Answer:

  Home  MS SQL Server

⟩ How to set database to be READ_ONLY in MS SQL Server?

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 GlobalGuideLineDB

GO

INSERT Links (Name) VALUES ('www.rendc.org')

GO

(1 rows affected)

ALTER DATABASE GlobalGuideLineDB SET READ_ONLY

GO

INSERT Links (Name) VALUES ('www.rendc.org')

GO

Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1

Failed to update database "GlobalGuideLineDB" because

the database is read-only.

SELECT * FROM Links

GO

Name

www.rendc.org

ALTER DATABASE GlobalGuideLineDB SET READ_WRITE

GO

INSERT Links (Name) VALUES ('www.rendc.org')

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.

 138 views

More Questions for you: