5

Good day. I explain my scenario. There are two users (user1 and user2). I have a table (tblTest) in SQL server with 3 columns (f1,f2,f3). What I want to do is the following:

  1. Deny UPDATE to column f2 and grant UPDATE to columns f1 and f3 for user1.

  2. Grant UPDATE to column f2 and deny UPDATE to columns f1 and f3 for user2.

For the moment I'm stuck at step 1).

This is what I did:

In SQL Server I selected tblTest => Properties => Permissions, selected user1.

In "Permissions for user1" at the bottom => Update then "Column Permissions" button, checked on Deny for f2 and Grant for f1 and f3.

Then I accessed SQL Server with user1's credentials and tried to update a row in this table.

Obviously the command failed with this message:

The UPDATE permission was denied on the column 'f2' of the object 'tblTest', database 'dbremarksSQL', schema 'dbo'.

Everything worked as it should so far. My problem is that also the other columns are not updated. I'd like the update command would update the f1 and f3 columns, at least, and denying the update just to f2 (leaving as it was).

Is that possible?

Thank you

btw I use SQL Server 2014 Management Studio.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LukeLuke
  • 71
  • 1
  • 8
  • Are you trying to update several columns - including `tblTest.f2` - in a single `UPDATE` statement, and then you expect that `f2` is not updated, but the other columns are? That's not how SQL Server works - if you execute a statement that is failing because of a lack of permissions, **nothing** of that statement is executed! If you want to update other columns as `User1` - use an appropriate `UPDATE` statement that **doesn't mention** the `f2` column! – marc_s Oct 29 '15 at 12:26
  • Fine! I got you. Thanks for the clarification. Then in this case, I think I cannot use the following instruction in my future vb.net program: MyDatabaseBindingSource.EndEdit() MyDatabaseTableAdapter.Update(MyDataSet.tblTest) because it's a single UPDATE statement. Am I correct? INFO: the above scenario was a troubleshooting to understand why this MyDatabaseTableAdapter.Update(MyDataSet.tblTest) didn't work with the same effect. – LukeLuke Oct 29 '15 at 12:35

2 Answers2

4

You can try like this:

GRANT UPDATE ON dbo.tblTest(f1,f3) TO user1;
DENY UPDATE ON dbo.tblTest(f2 ) TO user1;
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • I tried. But result is the same with the same message from server. Columns f1 and f3 are not updated. – LukeLuke Oct 29 '15 at 11:58
  • @LukeLuke:- Did you check that your column has the update permission? Looks like you have set the DENY update permission for f1 and f3 as well. – Rahul Tripathi Oct 29 '15 at 12:00
1

Have you tried this :

grant update(column-name) on table-name to user-name

font: Grant alter on only one column in table

Community
  • 1
  • 1
David
  • 1,147
  • 4
  • 17
  • 29
  • I tried. But result is the same with the same message from server. Columns f1 and f3 are not updated. – LukeLuke Oct 29 '15 at 12:11