How to set some tables in the SQLServer database to read-only

Generally, you need to set the database to read-only in the following situations:
1. Insert, Update, and Delete triggers
2. Check constraints and Delete triggers
3. Set the database to read-only
4. Put the table in a read-only file group
5. Reject object-level Permissions
6. Create a view
Before starting, create a database and table as an example:
Copy codeThe Code is as follows:
Create database MyDB
Create table tblEvents
(
Id int,
LogEvent varchar (1000)
)
Insert into tblEvents
Values (1, 'password Changed '), (2, 'user dropped'), (3, 'Finance Data Changed ')

Nsert/Update/Delete triggers:
Please note that INSTEADOF trigger is used here, because if you use AFTER trigger, the lock will be requested when executing DELETE, UPDATE, and INSERT statements, performance impact on write transaction logs and rollback operations
Copy codeThe Code is as follows:
Create trigger trReadOnly_tblEvents ON tblEvents
Instead of insert,
UPDATE,
DELETE
AS
BEGIN
RAISERROR ('tblevents table is read only. ', 16, 1)
ROLLBACK TRANSACTION
END

When you execute insert/update/delete, the following error is prompted:
Msg 50000, Level 16, State 1, Procedure trReadOnly_tblEvents, Line 7 tblEvents table is read only. msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. the batch has been aborted.

Use Check constraints and Delete triggers:
Now, add a check constraint "1 = 0" to the table, which means that the check always fails. It prohibits you from executing INSERT or Delete operations on any row.
First, disable the trigger created in the previous step: disable trigger trReadOnly_tblEvents on tblevents, and then ADD the CONSTRAINT: alter table tblEvents with nocheck add constraint chk_read_only_tblEvent CHECK (1 = 0, whether you execute any INSERT/UPDATE statement, the following error message is displayed:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECKconstraint "chk_read_only_tblEvent". The conflict occurred indatabase "MyDB", table "dbo. tblEvents ".
The statement has been terminated.
However, this constraint does not affect the DELETE operation. Therefore, you need to create another DDL trigger:
Copy codeThe Code is as follows:
Create trigger trReadOnlyDel_tblEvents ON tblEvents
INSTEAD
DELETE
AS
BEGIN
RAISERROR ('tblevents table is read only. ', 16, 1)
ROLLBACK TRANSACTION
END

Set the database to read-only:
You can set the database to read-only to prohibit DDL/DML operations on the entire database. You can use the following statement:
Copy codeThe Code is as follows:
USE [master]
GO
Alter database [MyDB] SET READ_ONLY WITH NO_WAIT
GO

Put the table in a read-only file group:
You can create a table in a read-only file group:
Copy codeThe Code is as follows:
USE [master]
GO
Alter database [MyDB] add filegroup [READ_ONLY_TBLS]
GO
Alter database [MyDB] add file (NAME = n' mydb _ readonly_tables ', FILENAME = n' C: \ JSPACE \ myDBReadOnly. ndf', SIZE = 2048KB, FILEGROWTH = 1024KB) to filegroup [READ_ONLY_TBLS]
GO
DROP table tblEvents
Create table tblEvents
(
Id int,
LogEvent varchar (1000)
)
ON [READ_ONLY_TBLS]
Alter database [MyDB] modify filegroup [read_only_tstrap] READONLY
Any DML operation on the table is rejected and the following error message is returned:
Msg 652, Level 16, State 1, Line 1
The index "" for table "dbo. tblEvents" (RowsetId 72057594038845440) resides on a read-only filegroup ("read_only_tstrap"), which cannot be modified.

Deny object-level Permissions
You can use the DCL command to control user permissions. However, you cannot restrict advanced permission users (such as system admin and DatabaseOwner) in this step ):
Copy codeThe Code is as follows:
Deny insert, UPDATE, delete on tblEvents TO Jugal
Deny insert, UPDATE, delete on tblEvents TO Public

Create View
To replace direct table access, you can use the View:
Copy codeThe Code is as follows:
Create view vwtblEvents
As
Select ID, Logevent from tblEvents
Union all
Select 0, '0' where 1 = 0

In this view, UNION is used, which is used only when you ensure that there are a corresponding number of columns. In this example, the table has two columns, so two output columns are used. At the same time, you should also ensure that the data types are consistent.
When a user attempts to operate data through INSERT/UPDATE/DELETE, the following error message is returned:
Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function 'vwtblevents1 'failed because it contains a derived or constant field. msg 4426, Level 16, State 1, Line 1View 'vwtblevents1 'is not updatable because the definition contains a UNIONoperator.


Last step:
Check whether it is necessary to use these steps to set the table to read-only.
If a table is always read-only, you should put it in a read-only file group.