How to make linked tables to SQL Server views read only?

  • Thread starter Jeffrey Ganping Chen
  • Start date
J

Jeffrey Ganping Chen

Hi,

I have a MS Access as the front end, and create several linked table to SQL
Server views through ODBC. I thought by using the views on SQL Server, the
Access front end will have read only rights (that's what I want). However,
the Access front end has all the rights (insert, update, delete, select)
through the views.

How can I make the linked tables read only?

Thanks for your help.
 
S

Stefan Hoffmann

hi Jeffrey,
I have a MS Access as the front end, and create several linked table to SQL
Server views through ODBC. I thought by using the views on SQL Server, the
Access front end will have read only rights (that's what I want). However,
the Access front end has all the rights (insert, update, delete, select)
through the views.
Execute the following in the Query Analyzer for each view:

USE YourDatabase
REVOKE DELETE, INSERT, UPDATE ON YourView FROM YourUser

mfG
--> stefan <--
 
R

Rick Brandt

Jeffrey said:
Hi,

I have a MS Access as the front end, and create several linked table
to SQL Server views through ODBC. I thought by using the views on SQL
Server, the Access front end will have read only rights (that's what
I want). However, the Access front end has all the rights (insert,
update, delete, select) through the views.

How can I make the linked tables read only?

Thanks for your help.

Easiest way would be to replace the links with Passthrough queries (always
read only).
 
D

Douglas J. Steele

Using a pass-through query will actually usually be faster than using a
linked table. That's because pass-through queries execute on the server, and
only return the appropriate rows.
 
M

MJatAflac

That's what I always thought and have experienced with DB2 Databases but it
hasn't held true for me with a sql server DB I use as a datasource for much
of what I'm doing these days. Probably an issue with that particular database.

Thanks!
 
S

Stefan Hoffmann

hi,
That's what I always thought and have experienced with DB2 Databases but it
hasn't held true for me with a sql server DB I use as a datasource for much
of what I'm doing these days. Probably an issue with that particular database.
Depends of the caching mechanisms of the RDBMS. A passthrough query is
never precompiled, so it is probably using a bad execution plan.


mfG
--> stefan <--
 
M

Mattias Jonsson

Use a view instead unless every sql statement you submit is going to be
different. That would be bad for performance reasons anyway. Using a
view, you can also grant/revoke the applicable permissions for the user,
since that's what the original question was about. If you go with a
passthrough query and there are still full permissions in the server
then anybody (well, maybe not *anybody*) could use that same connection
to insert/update/delete.

So my vote is to make it a view unless you have a compelling reason not to!

Good luck,
Mattias

From: Stefan Hoffmann, on 3/24/2006 8:12 AM:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top