This is an Access 2000 database with a SQL Server 2000 back end. Up until now the database has been unsecured with everyone logging in as admin. Last week, I gave the Admin user a password, and created user accounts for everyone. Then I assigned permissions on all the database objects. All the users are members of the 'Users' group. So it is this group that I restricted the permissions on.
I have some DSNless queries which have the SQL Server connect string held in the 'Connect property'. What I am finding is that everyone except me was a getting a 'Select permission denied' error when their application attempts to open any of these DNSless queries. I can open them myself without a problem. I found this strange because everyone, including myself is opening the queries with the same SQL Server credentials. The only difference is that my user account has Administer permissions on these queries. The Users group has modify permissions, because my application sometimes modifies the SQL string.
I have found now that if I give the Users group administer permissions on my DSNless queries, then the problem goes away. Can anyone explain this please?
Ian
Edit: I am now finding that there is an eratic element to this. Sometimes assigning adminster permissions fixes the problem. Sometimes it doesn't. Wierd!
Another Edit. I edited my code so that it put the Connect string into a MsgBox immediately before opening the query. Everything looked fine, then I got the "Select permission denied" error again.
Third Edit: After initially finding that adminster permissions would allow my users to open these objects, They cannot open them at all now no matter what I do.
Start Free Trial
