Deny certain user from updating certain rows

Question

Say I have a table like this:

canModify | name | age 
------------------------
    1     | John | 24
    0     | Nick | 21

I want to grant update access to a specific user only where canModify=1

I know there is column level security, something like:

DENY UPDATE ON Table(column) TO user;

But I can't find anything like:

DENY UPDATE ON Table To user
WHERE Table.canModify = 1

Is there a trigger or something I can set up to do this?

score 0 · Answer 1 · answered May 12 '20 at 20:17

0

In my opinion, if you are stuck with sql server 2012, the Row Level Security is not going to work for you.

So you have two options: 1) application layer, which was already mentioned. 2) on database level, an trigger may work

answered May 12 '20 at 20:17

Vladislav Zalesak

1,521
1
10
15

score 0 · Accepted Answer · answered May 13 '20 at 12:57

You can create a view with something like :

create view MyView as
select * from YourTable where CanModify =1

Then you can grant insert, update, delete, select (the appropriate permission) on the view for the user.

With this, he will only be able to update row where the "canModify" =1

If ever he needs to be able to read all rows, then you can grant select only on the table itself and grant the update only on the view.

This may required code modification if ever you want to do this inside an application (but if that's the case, then you should put this logic in the app code instead of in SQL).

Deny certain user from updating certain rows

2 Answers2