How do you lock table (or view) fields from editing???

[Richard Holliingsworth]

Hello:

I have an Access 2K form I built from a SQL Server 7.0 view. I want to
lock certain fields in the database from users so they can see them on
the views and forms, but NOT be able to edit them.

I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books and
could not find how to do this.

Any advise will be greatly appreciated.

Thanks,
Richard

[Helmut Wöss]

Re: How do you lock table (or view) fields from editing???

with sql server it is easy. One possible way:
- start enterprise manager
- open your database
- go into tables/views
- right click on a table/view
- select all tasks -> manage grants
(i don't know if this is the name, i have only
the german version)
in this window you can select your view/table
in the combobox, you see all users with their
rights and on the bottom you find the button
'columns' where you can set the rights for
a specific user on a specific row.

hth,
Helmut




"Richard Holliingsworth" <william.r.holl ingsworth@boein g.com> schrieb im
Newsbeitrag news:HJMoFu.7w1 @news.boeing.co m...[color=blue]
> Hello:
>
> I have an Access 2K form I built from a SQL Server 7.0 view. I want to
> lock certain fields in the database from users so they can see them on
> the views and forms, but NOT be able to edit them.
>
> I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books and
> could not find how to do this.
>
> Any advise will be greatly appreciated.
>
> Thanks,
> Richard
>[/color]

[Simon Hayes]

Re: How do you lock table (or view) fields from editing???

Richard Holliingsworth <william.r.holl ingsworth@boein g.com> wrote in message news:<HJMoFu.7w 1@news.boeing.c om>...[color=blue]
> Hello:
>
> I have an Access 2K form I built from a SQL Server 7.0 view. I want to
> lock certain fields in the database from users so they can see them on
> the views and forms, but NOT be able to edit them.
>
> I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books and
> could not find how to do this.
>
> Any advise will be greatly appreciated.
>
> Thanks,
> Richard[/color]

From the MSSQL side, you need to ensure that your users can SELECT
from the tables, but not UPDATE/INSERT/DELETE. Have a look at GRANT
and REVOKE in Books Online for more information. You should probably
also have a look at the information on database roles as well - you
can add users to the role, then grant the permissions to the role,
which makes things easier to manage.

Simon

[Richard Holliingsworth]

Re: How do you lock table (or view) fields from editing???

Helmut you are close. I went to the table/view and right clicked and
got a menu. From the "All Tasks" selection, I got another menu that
included "Manage permissions" not "Manage Grants"
I selected that option and got the user permissions for the table but
NOT for the columns

So, I still cannot lock fields (columns) on a table/view.

The first responder misunderstood my question. I am not trying to lock
the users from the table/view. They MUST be able to edit certain fields
bot NOT all of them. So I must be able to lock fields from update, NOT
the whole table.

Thanks for any other guidance.

Richard

Helmut Wöss wrote:
[color=blue]
>with sql server it is easy. One possible way:
>- start enterprise manager
>- open your database
>- go into tables/views
>- right click on a table/view
>- select all tasks -> manage grants
> (i don't know if this is the name, i have only
> the german version)
>in this window you can select your view/table
>in the combobox, you see all users with their
>rights and on the bottom you find the button
>'columns' where you can set the rights for
>a specific user on a specific row.
>
>hth,
>Helmut
>
>
>
>
>"Richard Holliingsworth" <william.r.holl ingsworth@boein g.com> schrieb im
>Newsbeitrag news:HJMoFu.7w1 @news.boeing.co m...
>
>[color=green]
>>Hello:
>>
>>I have an Access 2K form I built from a SQL Server 7.0 view. I want to
>>lock certain fields in the database from users so they can see them on
>>the views and forms, but NOT be able to edit them.
>>
>>I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books and
>>could not find how to do this.
>>
>>Any advise will be greatly appreciated.
>>
>>Thanks,
>>Richard
>>
>>
>>[/color]
>
>
>
>[/color]

[Simon Hayes]

Re: How do you lock table (or view) fields from editing???


"Richard Holliingsworth" <william.r.holl ingsworth@boein g.com> wrote in
message news:HJoJHt.3t7 @news.boeing.co m...
Helmut you are close. I went to the table/view and right clicked and got a
menu. From the "All Tasks" selection, I got another menu that included
"Manage permissions" not "Manage Grants"
I selected that option and got the user permissions for the table but NOT
for the columns

So, I still cannot lock fields (columns) on a table/view.

The first responder misunderstood my question. I am not trying to lock the
users from the table/view. They MUST be able to edit certain fields bot NOT
all of them. So I must be able to lock fields from update, NOT the whole
table.

Thanks for any other guidance.

Richard



I may have misled you by not mentioning that GRANT/REVOKE can be applied to
columns as well as to entire tables (although only SELECT/UPDATE apply at a
column level, of course). The Enterprise Manager functionality that Helmut
described is simply a graphical interface to GRANT/REVOKE. I don't have
SQL7, but the SQL2000 permissions screen has a button at bottom left called
'Columns', which allows you to manage column level permissions.

In Query Analyzer you would do something like this:

grant select on dbo.MyTable (col1, col2, col3) to MyRole
grant select, update on dbo.MyTable (col4, col5) to MyRole

Simon