CODEWITHSUNDEEP
sql-serversql-updateuser-permissionsrestrict
LukeLuke
LukeLuke

Reputation: 91

SQL Server - Restrict UPDATE to specific columns

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.

Upvotes: 7

Views: 6921

Answers (2)

David
David

Reputation: 1145

Have you tried this :

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

font: Grant alter on only one column in table

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try like this:

GRANT UPDATE ON dbo.tblTest(f1,f3) TO user1;
DENY UPDATE ON dbo.tblTest(f2 ) TO user1;

Upvotes: 6

Related Questions