ODBC Connection to SQL7 needs to be read-only

[ChrisN]

Hi,

I have a SQL7 database which I want Access to link to using ODBC. However, I need this link to be Read-Only regardless of the rights that the user may have to the SQL database.

Is there an easy way to force this? Infact, is there a way at all?

Thanks in advance.
Chris

 

[bassguy]

I have a few Ideas that might help....you could create an application role that is only allowed to activate stored procedures on your database (lock it out of all select,insert...etc)....then just create stored procedures to do all of the viewing.....the app will log in as the app and the user will not matter

just a thought

Bassguy

 

[balves]

The user will have to connect via an account that only has read access to the data. I don't believe there's anything in the ODBC Administrator that allows you to specify the data access level.

 

[ChrisN]

Thanks guys,

I have worked out something anyway. I have set the initial user logins to all be read-only therefore if they link the tables to access for reporting everything will be fine.

When they login to the system however, using there SQL accounts it opens read-only but then I have two tables. In the first it finds the user and the role that applies to them. Then in the second it finds that role and picks up a fixed account name and password, closes the database then re-opens the connection using the new username and password.
(all this is done in my code!)

Seems to work fine and fast too!!

Thanks for your help.

Chris