Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to set some tables in SQLServer database for read-only mode". In daily operation, I believe many people have doubts about how to set some tables in SQLServer database for read-only mode. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "how to set some tables in SQLServer database for read-only mode". Next, please follow the editor to study!

In general, there are several situations that require you to make the database read-only: 1. Insert,Update,Delete trigger 2. Check constraint and Delete trigger 3. Set the database to read-only 4. Put the table in the read-only filegroup 5. Deny object-level permissions 6. Before creating a view, create a database and table as an example: the copy 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 trigger: please note that INSTEADOF trigger is used here, because if you use AFTER trigger, you will request a lock when executing DELETE, UPDATE and INSERT statements, which will have a performance impact on writing transaction logs and rollback operations. The copy code is as follows: CREATE TRIGGER trReadOnly_tblEvents ON tblEvents INSTEADOF INSERT, UPDATE, DELETE AS BEGIN RAISERROR ('tblEvents table is read only.', 16,1) ROLLBACK TRANSACTION END

When the user executes insert/update/delete, the following error will be prompted: Msg 50000, Level 16, State 1, Procedure trReadOnly_tblEvents, Line 7tblEvents 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 it always fails. It forbids you from performing INSERT or Delete operations on any line. First, disable the trigger created in the previous step: disable trigger trReadOnly_tblEvents on tblevents, then add constraints: ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK (1 = 0) after execution, any INSERT/UPDATE statement you execute will prompt the following error message: 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 will not affect the DELETE operation, so you need to create another DDL trigger: copy the code as follows: CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents INSTEAD OF 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, which disables DDL/DML operations on the entire database. You can use the following statement: copy the code as follows: USE [master] GO ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT GO

Put the table into a read-only filegroup: you can create a table in a read-only filegroup: copy the code as follows: USE [master] GO ALTER DATABASE [MyDB] ADD FILEGROUP [READ_ONLY_TBLS] GO ALTER DATABASE [MyDB] ADD FILE (NAME = Numbermydbreadonlyloaded tables, FILENAME = NumberC:\ 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_TBLS] READONLY any DML operation on the table will be rejected And return the following error message: Msg 652, Level 16, State 1, Line 1 The index "" for table "dbo.tblEvents" (RowsetId 72057594038845440) resides on a read-only filegroup ("READ_ONLY_TBLS"), which cannot be modified.

Denying object-level permissions can control user permissions through the DCL command, but this step does not restrict advanced permission users (such as system admin,DatabaseOwner): copy the code as follows: DENY INSERT, UPDATE, DELETE ON tblEvents TO Jugal DENY INSERT, UPDATE, DELETE ON tblEvents TO Public

To create a view instead of directly accessing the table, you can use the view: copy the code 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, only if you make sure 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 make sure that the data types are consistent. When a user tries to manipulate data through INSERT/UPDATE/DELETE, he will get the following error message: Msg 4406, Level 16, State 1, Line 1Update or insert of view or function 'vwtblEvents1' failed because it contains a derived or constant field.Msg 4426, Level 16, State 1, Line 1ViewblEvents1' is not updatable because the definition contains a UNIONoperator. The final step: confirm that it is necessary to use these steps to make the table read-only. If a table is always read-only, then you should put it in a read-only filegroup.

At this point, the study on "how to set some tables in SQLServer database to read-only mode" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.