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?

  • I think usually you'd want to do this on the application layer. I can give you a rough example of where to start if this is something you'd consider doing. – Cowthulhu May 12 '20 at 20:07
  • @Cowthulhu as in, check the column value before proceeding with an update? – Kenta May 12 '20 at 20:08

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

  • 1
    I like this solution a lot, simple and clean in my opinion. Thanks! – Kenta May 19 '20 at 13:12

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

  • is there something in sql server 2016 that allows this? – Kenta May 12 '20 at 20:24
  • @Kenta yes, Row-Level Security was introduced in 2016 which would allow you to do this. But it's not a "free lunch" - there's some setup involved, and may exact a performance penalty. – alroc May 12 '20 at 20:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.