Granting UPDATE for only certain columns in a table

I have tried using the SQL statement shown below to grant UPDATE permissions for a single column in a single table to a user with db_datareader privileges.

grant update (col_1) on trs.dbo.table_1 to calc

When I then run a SQL script that has an UPDATE for col_1 on trs.dbo.table_1, I get an error message

Thanks for your support.

Here is a small bit of code that is able to produce the problem. It seems to be associated with having a JOIN in the UPDATE statement. An UPDATE without the JOIN works just fine.

create table trs.dbo.people
(
name varchar(10),
sex varchar(10),
age smallint
)

insert into people values ('tom', 'male', 10)
insert into people values ('jane', 'female', 18)
insert into people values ('sue', 'female', 22)

create table trs.dbo.grads
(
name varchar(10),
grad_yr varchar(4)
)

insert into grads values ('jane', '2006')
insert into grads values ('sue', '2002')

grant update (age) on trs.dbo.people to calc

Then, from the db_datareader account, calc, run the following UPDATE statements, one at a time.

update p
set age = 40
from trs.dbo.people p
where (age is not null)
;

update p
set age = 35
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)
;

The first one works. The second one fails with error message

Rob,

If I enter SELECT statements against the two tables, while connected as "CALC", all the rows from each table are returned.

select * from grads;

select * from people;

I have also tried using the DB_OWNER connection to explicity GRANT SELECT access to those tables, and still get the same error message when I try to update the AGE column, as in the example above, while connected as "CALC".

The same code runs just fine with SQL Server 2000.  I only ran into a problem when I tried moving it over to SQL Server 2005.

If I grant UPDATE access on the NAME column, as identified in the Error message, then the code runs.

But I hate to do that, since CALC is supposed to be a relatively "unprivileged" user, with db_datareader general privileges only, and CREATE TABLE privileges -- and in my actual application, these other columns are the PRIMARY KEY columns in the tables.

Adding a new column to the table, a column to which CALC has UPDATE permission, was a way of avoiding creating an entirely new table for CALC to own, with 500,000 rows, and a 20-byte primary key that incorporates 5 columns.  Had I created such a new table my processing queries would have to JOIN this table to the 500,000 row counterpart.  Instead of such a JOIN I added a new column as a simpler, faster solution.

Dan

This seems like such a "natural" thing to have to do, in most any application that requires user permissions to only certain columns in a table.

Consider a table that connects salary to social security number. If you want to allow someone to edit SALARY, must you also allow them to edit SOCIAL SECURITY NUMBER

What if you are running a medical office and have some sort of PATIENT NUMBER in the medical records. If you want to edit information on their account, such as their ZIP CODE, must you also allow them to edit the PATIENT NUMBER

Is there maybe a greater need in SQL Server 2005 to create a new ROLE for every different set of permissions that one might need in the database, rather than use GRANT UPDATE to customize access to different users

I'm not now at my office where I could try it, but I'm wondering if some SYSTEM table stores UPDATE permissions, and if a user has NOT received GRANT or DENY UPDATE permissions on a column in a table, maybe an INNER JOIN is being used, rather than an OUTER JOIN on the permissions table. If that is true, I suppose I can DENY UPDATE on the NAME column in the example, and the query should work, because it finds an entry in the permissions table for the NAME column, even though that entry is "DENY." So tomorrow at the office I'll try DENY UPDATE (name) on TRS.DBO.PEOPLE to CALC. (I'll have to check the syntax on the DENY statement. I think I noticed one in the past few days.)

Dan

Louis,

Thanks for posting that to the MS "feedback" site.

I tried my "DENY UPDATE" idea, but it didn't fix anything: the same error message was obtained for the same UPDATE statement.

There aren't many places in my code where I am performing updates on columns where the GRANT UPDATE permission is limited to certain columns -- maybe a few dozen. I'll just make the code edits corresponding to your suggestion, and maybe un-do them if/when a patch occurs.

Thanks again.

Dan

Louis,

I reworked my code for SQL Server 2005, using the technique you suggested (or something quite similar):

update p set age = 35
from trs.dbo.people p
where p.name in (select p2.name
from trs.dbo.people p2
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null))

In all but a single instance, this solution worked just fine.

In the remaining instance, the value that I need for the SET clause is from the JOINed table. Were we using the example I supplied (rather than my actual code), this could appear as

update p
set age = cast(g.grad_yr as int) + 18 - 2006
from trs.dbo.people p
inner join trs.dbo.grads g
on p.name = g.name
where (age is not null)

I didn't see any easy way around this, other than to create a CURSOR on "select distinct GRAD_YR from GRADS" and using a LOOP over the CURSOR values, and having code like

update p
set age = @grad_yr + 18 - 2006
from trs.dbo.people p
where (age is not null) and p.name in (select distinct name from trs.dbo.grads where grad_yr = @grad_yr)

I am thankful that the number of values for my CURSOR is less than 10, in my actual application.

If you can think of a better alternative, I would be happy to learn of it.

Thanks.

Dan