Sql Server User Read Only Access to Tables

By:   |   Updated: 2012-06-15   |   Comments (19)   |   Related: More > Database Administration

Free MSSQLTips Webinar: How to Avoid Worst Practices as a SQL Server DBA

Learn from 20 years of feel about how to turn SQL Server Worst Practices such as database corruption, security, scaling, monitoring, deject toll direction, and SQL Agent monitoring into Best Practices to properly manage your database surroundings.

Problem

In some cases there may be a demand to make a SQL Server table read only. At that place are several different options for doing this and in this tip we encompass various ways that you can brand a tabular array read just in a SQL Server database.

Solution

There may be requirements where we accept to make specific tables read just. Yous can make a specific table in database read only by using one of the below techniques. For these examples, nosotros will apply database MyDB and table tblEvents for all of the examples.

1. Insert, Update, Delete Trigger
2. Cheque Constraint and Delete Trigger
3. Make the Database Read Only
4. Put the Table in a Read Just File Group
5. DENY Object Level Permission
6. Create a View

To setup the examples, execute the beneath script to create the sample database and table.

create database MyDB create table tblEvents (   id int,   logEvent varchar(1000) ) insert into tblEvents values (1, 'Password Inverse'), (2, 'User Dropped'), (iii, 'Finance Data Changed')          

Insert/Update/Delete Trigger

Delight note that I take used an INSTEAD OF trigger.  If yous apply an After trigger it will actually execute the DELETE, UPDATE or INSERT statement which will require locking, writes to transaction log and a rollback which could impact performance.

CREATE TRIGGER trReadOnly_tblEvents ON tblEvents     INSTEAD OF INSERT,                UPDATE,                DELETE As BEGIN     RAISERROR( 'tblEvents table is read only.', 16, one )     ROLLBACK TRANSACTION End          

Whenever a user executes an INSERT/UPDATE/DELETE statement, the transaction will fail with the below error.

Msg 50000, Level 16, Country 1, Procedure trReadOnly_tblEvents, Line 7 tblEvents table is read only. Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted.          

Using Check Constraint and Delete Trigger

Here we will add a check constraint on the table with the expression 1=0, which will always be false. It volition not allow yous to do an INSERT or UPDATE on whatever rows.

Hither we will first disable the trigger created in the previous step using the beneath script.

disable trigger trReadOnly_tblEvents on tblevents          

Add the Bank check Constraint using the below script.

ALTER TABLE tblEvents WITH NOCHECK ADD CONSTRAINT chk_read_only_tblEvent CHECK( 1 = 0 )          

Whenever you execute an INSERT/UPDATE query, it will fail with the below mistake.

Msg 547, Level 16, State 0, Line one
The UPDATE statement conflicted with the Cheque constraint "chk_read_only_tblEvent". The conflict occurred in database "MyDB", table "dbo.tblEvents".
The statement has been terminated.

Just the check constraint will not prevent a DELETE operation. To stop the DELETE, you will also need to create a DDL trigger as shown below.

CREATE TRIGGER trReadOnlyDel_tblEvents ON tblEvents     INSTEAD OF                 DELETE AS BEGIN     RAISERROR( 'tblEvents table is read only.', 16, 1 )     ROLLBACK TRANSACTION End          

Make the Database Read Only

You can make the database read merely and it will non allow whatsoever DDL/DML operations for the entire database. Execute the below query to brand the database read only.

Use [master] Become ALTER DATABASE [MyDB] SET READ_ONLY WITH NO_WAIT GO          

Put the Table in a Read Only File Group

Here nosotros will create the table in a split up filegroup and brand the filegroup read only.

USE [principal]
Go
ALTER DATABASE [MyDB] ADD FILEGROUP [READ_ONLY_TBLS]
Become
Modify 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
Drib table tblEvents
create table tblEvents
(
id int,
logEvent varchar(one thousand)
)
ON [READ_ONLY_TBLS]
Modify DATABASE [MyDB] MODIFY FILEGROUP [READ_ONLY_TBLS] READONLY

Any DML operation against the tabular array volition fail with the below error.

Msg 652, Level 16, State 1, Line one
The index "" for tabular array "dbo.tblEvents" (RowsetId 72057594038845440) resides on a read-only filegroup ("READ_ONLY_TBLS"), which cannot be modified.

DENY Object Level Permission

Yous can control user permissions by using DCL commands, however it will non prevent users with elevated permissions (for example System Admin, Database Possessor).

DENY INSERT, UPDATE, DELETE ON tblEvents TO Jugal DENY INSERT, UPDATE, DELETE ON tblEvents TO Public          

Create a View

Instead of giving admission to the table, you can use a view.  The view below would forbid any DML operations on it.

create view vwtblEvents every bit select ID, Logevent from tblEvents matrimony all select 0, '0' where i=0          

For this view I have added a UNION.  If you use this approach you will need to make sure in that location are a matching number of columns that are output for each of the queries.  In this example there are 2 columns, and then I have 2 output columns for both queries.  Also, you need to make certain the data types friction match as well.

When a user tries to perform an INSERT/UPDATE/DELETE functioning they volition go the below errors.

Msg 4406, Level 16, Land ane, Line i Update or insert of view or function 'vwtblEvents1' failed because it contains a derived or constant field. Msg 4426, Level xvi, State one, Line ane View 'vwtblEvents1' is not updatable considering the definition contains a UNION operator.          

Next Steps

• If you lot have a need to make a table read only remember these unlike techniques.
• If a table will always exist read just, y'all should just motility that to a read only filegroup.

Related Articles

Popular Articles

Well-nigh the writer

Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips

Article Final Updated: 2012-06-15

Sql Server User Read Only Access to Tables

Source: https://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/

Share this post