Setting up user group that can create and save select queries

[Guest]

I want to set up a user group that can open the database and the members can
write their own queries but not be able to change any data. I have worked on
the permissions and am finally able to have the users in the group create
queries but they cannot save them as they cannot use MSysTables. I cannot
change permissions of this system table as its owner is 'Engine'. Is there
any way to set this up?

I really have three databases:
1) The data database

2) The application database used to enter and update the data; it is linked
to the data database. It has a user table used in conjunction with Access's
use-level security that controls the users & their options.

3) A third database that is also linked to the data database. It also has a
user table used in conjunction with Access's user-level security. But once a
user is allowed on this dataazsbe a database window is displayed. I want to
set permissions so the users can write select queries and then save them for
writing reports. I am able to have them create queries, and have observed
that they cannot change the data in the query output (as desired) BUT they
cannot save the query. The following messages results:
You do not have the necessary permissions to use the 'MSysTables' object.
Have your system administrator who created this object establish the
appropriate permissions for you.'

I have looked into changing the permission for this table but the owner is
"Engine", and even as the database owner I cannot make changes to the
permissions for this table.

Any suggestions? Can I even do what I am trying to do? Basically I want
some users to create their own queries and reports without being able to
accidentally change the data.

CAM

 

[Joan Wild]

See www.jmwild.com/MSysTables.htm

You are having this problem because of a bug with the security wizard.

Put your users in one of the wizard-created groups, as well as the group you created for them, and they'll be able to save their queries.

Another possibility is to set up RWOP queries. Then you can modify the SQL property of these queries, rather than create/save new queries. Give the users modify permissions on the query to change the SQL property in code.

 

[Guest]

Thanks for the suggestions. I will try the first one and hope that works.
What are RWOP queries?

 

[Joan Wild]

'Run with owner permissions' - See www.jmwild.com/RWOP.htm for more information.

 

[Guest]

I want to thank you for all your help. I ended up creating a new workgroup
file manually and then recreated the databases within the new workgroup file
- copying in all the objects from the old databases. It was time consuming
but now all is working well.

Thanks again