SQLServer Tips: How To Make a Table Read Only in Database
Impetus
The impetus for writing this article was a question posted in the forums asking on the different methods in which a table can be made readonly in the database. Since then there have been other questions as well on the same topic. This is the inspiration behind coming up with this article
Business Case
Most often there are requirements in project which requires data to be protected from modification. Examples of this type of scenarios are fiscal Year details in calendar, sales data targets etc. These data would be owned by various departments and once it is set for the period then it should be protected against modification unless its done by authorized departments. In such cases we can make use of one of the below mentioned methods to make the data read only and avoid its modification.
Method 1 : Making the associated file group READ ONLY
In this method we apply the following steps to make the table data read only
- Create a new file group
- Create the table on the file group
- Populate the table with the required data
- ALTER the file group to make it read only
This approach can be illustrated using sample code as below
--Create file group and associated file
ALTER DATABASE <database name>
ADD FILEGROUP MyReadOnlyFG;
GO
ALTER DATABASE <database name>
ADD FILE
(
NAME = ROFG,
FILENAME = '<Installation Drive> :\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyReadOnlyFile.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP MyReadOnlyFG;
GO
--Create table on the file group
CREATE TABLE financetargets
(
ID int IDENTITY(1,1),
YearVal int,
QuarterVal int,
TargetVal int
) ON MyReadOnlyFG
--populate some sample data
INSERT financetargets
VALUES (2014,1,1789),
(2014,2,3000),
(2014,3,1100),
(2014,4,2340),
(2015,1,990),
(2015,2,3420),
(2015,3,2333),
(2015,4,1123)
-- Make the file group read only
ALTER DATABASE <databasename> MODIFY FILEGROUP MyReadOnlyFG READ_ONLY
--Test the result
--This will fail with error stating data cant be modified
INSERT financetargets
VALUES (2016,1,1956)
See screenshot below
Pros
- Easier to manage
- Can be easily adapted to new objects by just moving them to read only file group
- No need of separate user access manipulations
Cons
- The user should have at least dbcreator role privileges on the server for implementing the above steps.
Method 2: Using explicit DENY statement to avoid DML operations
In this case we can execute an explicit DENY operation to prevent data modification in the table.
This can be done as per below illustration
--creating sample table financetargets123 for the purpose
CREATE TABLE financetargets123
(
ID int IDENTITY(1,1),
YearVal int,
QuarterVal int,
TargetVal int
)
INSERT financetargets123
VALUES (2014,1,1789),
(2014,2,3000),
(2014,3,1100),
(2014,4,2340),
(2015,1,990),
(2015,2,3420),
(2015,3,2333),
(2015,4,1123)
creating a new user with restricted priviledges
Now explicitly deny insert,update,delete permissions to the new user on the object
DENY INSERT, UPDATE, DELETE ON financetargets123 TO Restricted
Try doing some DML operations and you will get error message as below
If you want this to applied for multiple users the recommended way is to create a role first and deny it required actions. Then just add users as members to the role.
Pros
- Permissions for DML actions can be selectively or separately applied for users
Cons
- Management effort involved is huge. Each user has to be denied access / added to created role separately
- Admin level users or users with elevated permissions cannot be restricted access using this method
Method 3: Defining INSTEAD OF TRIGGER on the table
An INSTEAD OF TRIGGER can also be defined on the table to restrict data manipulation on it by the users.
This illustration for this approach can be found below
--Create sample table
CREATE TABLE FinancialTargetsForTrigger
(
ID int IDENTITY(1,1),
YearVal int,
QuarterVal int,
TargetVal int
)
--populate table with sample data
INSERT FinancialTargetsForTrigger
VALUES (2014,1,1789),
(2014,2,3000),
(2014,3,1100),
(2014,4,2340),
(2015,1,990),
(2015,2,3420),
(2015,3,2333),
(2015,4,1123)
--create the INSTEAD OF Trigger to make table read only
CREATE TRIGGER trg_FinancialTargetsForTrigger
ON FinancialTargetsForTrigger
INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
RAISERROR ('This table is read only and cant be modified',16,1)
END
--Try any DML operations
Pros
- No need to tweak privileges for each user or role
- Doesn't need to explicitly fiddle with the permissions
Cons
- The overhead of using a trigger
You can also achieve the same effect using a combination of INSTEAD OF DELETE and a CHECK constraint based on a trivial false condition like say 1=0 for INSERT/UPDATE operations.
Method 4 : Adding a Columnstore Index on the Table
Another method which can be implemented in SQL 2012 version to make a table readonly is to define a columnstore index in it. This is applicable only in SQL 2012 version and can be implemented in enterprise editions. Once columnstore index is added the table will become read only in 2012. Thereafter if you do any DML operations it will fail.
CREATE NONCLUSTERED COLUMNSTORE INDEX [ColumnStore__FinancialTargets]
ON [dbo].[FinancialTargets]
([YearVal] , [QuarterVal])
Now try doing a DML operation as below
DELETE FROM FinancialTargets WHERE ID = 4
You will get a error as below
Msg 35330, Level 15, State 1, Line 1
DELETE statement failed because data cannot be updated
in a table with a columnstore index. Consider disabling the
columnstore index before issuing the DELETE statement,
then rebuilding the columnstore index after DELETE is complete.
Indicating that table has become read only
Pros
- Very little administration effort required for setup
Cons
- This feature is available only in SQL Server 2012 Enterprise Edition
Summary
As seen from above approaches there are multiple ways to make a table read only in a user database. The choice of the approach has to be made based on criteria such as
- Admin effort that can be spend for the activity
- Range of users to which restriction is to be imposed on
- Allowable overhead on the database