Grant insert, but only allow entering vals in some fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cbtechlists@gmail.com - 26 Jan 2007 22:19 GMT

I have a table with 3 fields:
val1
val2
val3

Is there a way using a role to allow users to create a new record in
the table, but only allowing them to populate the val1 field during the
insert? They should not be allowed to put data in fields val2 and val3
during the insert and they should only be allowed to modify the val1
field.

Thanks!

Chris

Reply to this Message

Erland Sommarskog - 26 Jan 2007 23:23 GMT

> I have a table with 3 fields:
> val1
[quoted text clipped - 6 lines]
> during the insert and they should only be allowed to modify the val1
> field.

You could a create view that exposes the permitted column and let the
users insert into that view rather than directly to the table. Or you
could expose all columns in the table, and have an INSTEAD OF trigger
ignores the non-permitted columns.

... or you could use stored procedures.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message