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

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

 

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

 

[Rick Brandt]

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

 

[MJatAflac]

Are there response time issues with this approach?

 

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

 

[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!

 

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

 

[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: