Deny updates on a table but allow inserts

Sometimes it is nessesary to deny updates on a table, but allow inserts. This is the case when the history must never be changed, for instance in a table with accounting data.

This can be done in two different ways. The first method is to use permissions on the table and make sure everyone is denied update permissions on the table. The other method is to use an “instead of update” trigger.

Here I’ll show both methods.

Summary

I’ll start by showing the T-SQL for both solutions. Later in this blog post I’ll show examples on both methods.

To deny updates on a table:

DENY UPDATE ON OBJECT::[tablename] TO [loginname]

To create an instead of update trigger:

CREATE TRIGGER [triggername] ON [tablename] INSTEAD OF UPDATE

AS

RETURN
GO

Deny updates demo database

To simplify the following demonstration, I recommend that you login as sysadmin (and as always, not on a production server). Let’s start by creating a demonstration database with just one table. Also, we need to create a login and a user in our new database mapped to that login. Finally we give the user read and write permissions:

CREATE DATABASE BlockUpdatesNotInserts
GO

USE BlockUpdatesNotInserts
GO

CREATE LOGIN SQLTestLogin WITH PASSWORD=N'pwd', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER SQLTestLogin FOR LOGIN SQLTestLogin WITH DEFAULT_SCHEMA=dbo
GO

ALTER ROLE [db_datareader] ADD MEMBER [SQLTestLogin]
GO

ALTER ROLE [db_datawriter] ADD MEMBER [SQLTestLogin]
GO

CREATE TABLE dbo.Tbl_DenyUpdatesWithPermissions
	(
		COL1 INT IDENTITY(1,1) NOT NULL CONSTRAINT COL1_PK PRIMARY KEY CLUSTERED
		,COL2 VARCHAR(100) NOT NULL
	)
GO

Deny updates with permissions

We’re going to use the “EXECUTE AS” method to run under the permissions of the newly created login and see what happens when we modify the permissions on the table:

EXECUTE AS USER = 'SQLTestLogin'

Remember, the user SQLTestLogin currently has read and write permissions in the database. So we can do a insert without problems:

INSERT dbo.Tbl_DenyUpdatesWithPermissions (COL2) VALUES ('Insert1')

An update would work as well, since we haven’t modified the permissions on the table yet.

Now let’s deny updates on the table for user SQLTestLogin. But first we need to go back to our original login (to get more permissions) with the “REVERT” command:

REVERT;

DENY UPDATE ON OBJECT::dbo.Tbl_DenyUpdatesWithPermissions TO SQLTestLogin
GO

Now, if we try to update as user SQLTestLogin, we’ll get an error:

EXECUTE AS USER = 'SQLTestLogin';
UPDATE dbo.Tbl_DenyUpdatesWithPermissions SET COL2 = 'Update1' WHERE COL1 = 1

[red_box]Msg 229, Level 14, State 5, Line 1 The UPDATE permission was denied on the object ‘Tbl_DenyUpdatesWithPermissions’, database ‘BlockUpdatesNotInserts’, schema ‘dbo’.[/red_box]

Inserts and selects are ok however:

INSERT dbo.Tbl_DenyUpdatesWithPermissions (COL2) VALUES ('Insert2')

SELECT * FROM dbo.Tbl_DenyUpdatesWithPermissions

Note that this method will work even if you deny updates on a login that has server sysadmin permission! However, you’ll need to manage the permissions so that all users (new and edited) are denied update permissions.

To deny insert permissions as well, simply issue the following statement:

DENY INSERT ON OBJECT::dbo.Tbl_DenyUpdatesWithPermissions TO SQLTestLogin

A final note on the “EXECUTE AS” method. To see what credentials you are running under, issue the following statement:

SELECT SUSER_NAME(), USER_NAME();

Before we finish, remeber to revert the permissions:

REVERT;

Avoid updates with an instead of update trigger

This method is fool proof in the way that updates are disabled regardless of permissions. So if your routines miss that a new sysadmin has been created, he/she may be able to do updates until permissions are fixed. However, a trigger is easily disabled, so without permissions you won’t have any protection agains malicious users. If you want extra safety, use both metods.

The instead of trigger is easy to create. First, let’s create a demonstration table:

CREATE TABLE dbo.Tbl_DenyUpdatesWithInsteadOfTrigger
	(
		COL1 INT IDENTITY(1,1) NOT NULL CONSTRAINT COL1_PK2 PRIMARY KEY CLUSTERED
		,COL2 VARCHAR(100) NOT NULL
	)
GO

CREATE TRIGGER T_IO_U_Tbl_DenyUpdatesWithInsteadOfTrigger ON dbo.Tbl_DenyUpdatesWithInsteadOfTrigger INSTEAD OF UPDATE

AS

RETURN
GO

The trigger above simply ignores any updates on the table. But it could easily be modified to raise an error instead. Try to update a row with the following statement:

INSERT dbo.Tbl_DenyUpdatesWithInsteadOfTrigger (COL2) VALUES ('Insert')

UPDATE dbo.Tbl_DenyUpdatesWithInsteadOfTrigger SET COL2 = 'Update' WHERE COL1 = 1

You’ll see that the COL2 value still is “Insert1” efter the update:

SELECT * FROM dbo.Tbl_DenyUpdatesWithInsteadOfTrigger