Sql-server – Deny certain user from updating certain rows

permissionsrow-level-securitysql serversql-server-2012ssms

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?

Best Answer

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).