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

Msg 230, Level 14, State 1, Line 2100

UPDATE permission denied on column 'col_2' of object 'table_1', database 'TRS', schema 'dbo'.

Why is the error message referring to "col_2" when my SQL statement is trying to update "col_1"?

When I performed the "grant" I did it with an account that has db_owner, db_securityadmin, and db_ddladmin privileges.

This worked in SQL Server 2000. What must I do to get it to work in SQL Server 2005?

Sorry Dan - can you show us the update query?|||

You are going to have to dig deeper. The only scenario that I can think of where an update to another column causes an update to another column causing issues like this is with a trigger with dynamic SQL (a real no-no in almost all cases, but it could exist). The fact that you have appended database names to the ddl makes me curious as to how that *might* cause issue, but I don't even see how anything cross-database could be an issue either.

Here is a script that shows what I am meaning:

create table test
(
column1 int,
column2 int
)
go
create user fred without login
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
Msg 229, Level 14, State 5, Line 1
The UPDATE permission was denied on the object 'test', database 'tempdb', schema 'dbo'.
*/
go
revert
go
grant update (column1) on test to fred
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
(0 row(s) affected)
*/
update test
set column2 = 1
/*
Msg 230, Level 14, State 1, Line 1
The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'.
*/
go
revert
go
create trigger test$updateColumn1
on test
after update
as
begin
exec('
update test
set column2 = 2')
end
go
execute as user = 'fred'
go
update test
set column1 = 1
/*
Msg 230, Level 14, State 1, Line 2
The UPDATE permission was denied on the column 'column2' of the object 'test', database 'tempdb', schema 'dbo'.
*/

If you could post a full example like this showing your issue I think that you might find your error, or certainly one of us can help you out.

|||

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.

Using a DB_OWNER account perform the following table creation, insert, and grant commands:

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

Msg 230, Level 14, State 1, Line 1

UPDATE permission denied on column 'name' of object 'people', database 'TRS', schema 'dbo'.

Does this help?

|||Very nice. I have no answer as to why this is, but I will check around and let you know.|||Thanks!|||Do you have select permission on the other columns?

I know it's complaining that you don't have UPDATE permission... but it's complaining about your access to another column that's involved in the join.

Rob|||

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

grant select on trs.dbo.people to calc
grant select (name) on trs.dbo.people to calc
grant select on trs.dbo.grads to calc

Dan

|||Ok, so that idea wasn't right. ;)

It's somehow related to the fact that you're joining the table you're updating to another table, and the engine thinks that it needs to be able to update that column too. It doesn't of course, but the fact that the column is used in the query must be confusing it somehow.

I assume this works just fine if you grant update access to the name column too?

One workaround might be to wrap it up in a table expression, but if that works, it'll just be down to luck.

Rob|||

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

|||Hmm... I'll have to do some more hunting. I'd like to say "try using a cte or view", or something like that (based on the fact that you might be able to update a view without it thinking it needs to be able to update the joining column), but I actually don't know if that will help. I'll have to recreate the situation locally and try some things.

But hopefully some of the other guys will already know the answer to this.

Rob|||

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

|||

This is almost clearly a bug that I posted here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=257897

A (not terribly satisfying) workaround is to move the join to a subquery:

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

Here is the simple repro with users I posted:

create database trs
go
use trs
go
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')
go
create user calc without login
sp_addrolemember 'db_datareader','calc'
go
grant update (age) on trs.dbo.people to calc
go

--Then, from the db_datareader account, calc, run the following UPDATE
statements, one at a time.
execute as user = 'calc'
go

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

/*
Msg 230, Level 14, State 1, Line 1
The UPDATE permission was denied on the column 'name' of the object
'people', database 'trs', schema 'dbo'.
*/
--

|||

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