How to set database to be READ_ONLY in MS SQL Server?
Submitted by: AdministratorDatabases 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.globalguideline.com')
GO
(1 rows affected)
ALTER DATABASE GlobalGuideLineDB SET READ_ONLY
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
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.globalguideline.com
ALTER DATABASE GlobalGuideLineDB SET READ_WRITE
GO
INSERT Links (Name) VALUES ('www.globalguideline.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.
Submitted by: Administrator
* 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.globalguideline.com')
GO
(1 rows affected)
ALTER DATABASE GlobalGuideLineDB SET READ_ONLY
GO
INSERT Links (Name) VALUES ('www.globalguideline.com')
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.globalguideline.com
ALTER DATABASE GlobalGuideLineDB SET READ_WRITE
GO
INSERT Links (Name) VALUES ('www.globalguideline.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.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Convert Binary Strings into Integers in MS SQL Server? |
☺ | What Happens If NULL Values Are Involved in Bitwise Operations? |
☺ | What Happens If You Insert a Duplicate Key for the Primary Key Column in MS SQL Server? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
☺ | How To Use Subqueries with the IN Operators in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |