473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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

Jul 20 '05 #1
4 20865
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" <wi*********************@boeing.com> schrieb im
Newsbeitrag news:HJ********@news.boeing.com...
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

Jul 20 '05 #2
Richard Holliingsworth <wi*********************@boeing.com> wrote in message news:<HJ********@news.boeing.com>...
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


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
Jul 20 '05 #3
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:
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" <wi*********************@boeing.com> schrieb im
Newsbeitrag news:HJ********@news.boeing.com...

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



Jul 20 '05 #4

"Richard Holliingsworth" <wi*********************@boeing.com> wrote in
message news:HJ********@news.boeing.com...
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


Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: leecho | last post by:
Hi, recently, i was assigned as a new dba for our system. I found that my statistic keep change from time to table. To look for the cause, i wanna to lock a table, means only allow user to...
4
by: Brian Andrus | last post by:
Ok. I upgraded to MS Access 2003 recently and now I am having great heartache. In Access 2002, when I opened a table to view the data, there were wonderful little "plus" signs that I could click...
0
by: Colleyville Alan | last post by:
My app is giving me this error. Run-time error 3211: The database engine could not lock table 'Sorted_Template' because it is already in use by another person or process. When I run the app...
1
by: Sandy | last post by:
Hi I want to lock a table using JDBC as I want to perform some query's (read and write) in exclusive mode. Different threads will be executing the same code simultaneously. I am using the...
2
by: simonZ | last post by:
I create a transaction: sqlTran=sqlConn.BeginTransaction(IsolationLevel.Serializable); Then, I insert some data into report table with sqlCommand object: oCmd = new...
0
by: sang | last post by:
Hi I want to lock the table with both read and write there is no action held after lock the table. I know how to lock the table with read and write, lock table mytable read; lock table...
3
by: Himmel | last post by:
I have added a section of code to a function that is designed to copy data from several tables and place them into a single table. The new table already contains a unique ID and name, and I am...
2
by: robert stearns | last post by:
When I use LOCK TABLE ... COMMIT, must I also use odbc_autocommit(dbConn, FALSE)? In other words will auto commit prematurely defeat the LOCK TABLE?
3
by: Frederick Tant | last post by:
DB2 V9.7 The DB is used by 1 application with no concurrent applications. I got the problem that large update/insert cause lock escalation, to solve the issue I set a lock on the table so...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.