User has group permissions to object, but still denied access.

[Project Orca]

The user is attempting to change some queries on a database stored on a
server and accessed by a mapped drive, but gets the following error message
when trying to save the changes: "You do not have the necessary permissions
to use the '<query>' object. Have your administrator or the person who
created this object establish the appropriate permissions for you." (replace
the <query> with the name of the query currently being edited). There are
about 80 queries in the database, and I haven't been able to find a pattern
for which queries she is able to edit and which ones she is not.

If she copies or moves the database to her local machine, she's able to make
changes to any of the queries.

Regarding the security, the database was converted from an Access 97
database with no security to an Access 2000 (when connected to a workgroup
file - database.mdw as user "sysadm" ) and then to Access 2003 format (again
with the database.mdw and user "sysadm"). At that point, all of the objects
in the database had an owner of "sysadm".

Then the security permissions were set on the database. The Users group was
only given permission. Another group was created for basic read/write
(GroupRW). The Admins group was given full permissions. The Admin user was
removed from the Admins group. Sysadm was already in the Admins group, the
GroupRW and Users group. User1 had the same groups (Admins, GroupRW, and
Users).

The user has full control of the folder on the server where the database is
stored, as well as propagating that full control to the database files (mdb,
ldb, and mdw).

The way I understand the security as it is set up, even though the
individual users don't have permissions to database objects, they should
still get the permissions that were assigned to their group - in this case
full permissions for User1 since she is part of the Admins group and the
Admins group has full permissions to all database objects.

Can anyone see a reason why she wouldn't be allowed to edit a query? Can
anyone see a reason why she can edit some queries but not others (when they
all have the same security structure)?

I don't want to muddy the waters but it may be important to note that there
35 forms, 45 reports, and 2 macros. She's unable to gain exclusive access to
change reports and forms when the database is located on the server - even if
she's the only one in the database at the time and the server reports no
other users with the database open. This is another issue that I need to
resolve, but I'm trying to tackle one area at a time...

 

[Chris O'C via AccessMonster.com]

Since the permissions are applied to the db file and she has different
permissions depending on where the file is, it means she's either opening the
file as a different user or opening it with a different workgroup when she
opens it on her PC. Or it means she's not opening an exact copy of the db
file. (Maybe an older copy before she had permissions to these queries?)

She doesn't have exclusive access to make changes to the db because someone
accidentally named the workgroup file the same as the db file and placed both
files in the same folder. DBName.mdw and DBName.mdb will both create a
locking db file, DBName.ldb, when they open unless that file already exists.
If DBName.ldb already exists, the db will be opened in shared mode, not
exclusively.

Either change the name of one of the files or move one of the files to
another folder.

Chris
Microsoft MVP

 

[Project Orca]

Thanks for the heads-up on the file names! That fixed my second issue.

I'm still scratching my head over the first issue though. She's not using
different databases - I've made sure that I'm using the same database and
workgroup file in my tests under her username. I test them on the mapped
drive and she's unable to edit a few specific queries (security on those
specific queries is identical to queries that she does have the ability to
edit). I copy the files (mdb and mdw) to the local PC, reopen Access, attach
to the local mdw file, close Access, reopen Access and open the local mdb
file and I can edit all queries.

I checked the object dependencies for those queries, thinking that they may
be required for the reports that she couldn't edit (due to the mdb and mdw
file names being identical), but there was no common dependency among the
queries that she couldn't edit. The problem also continued after I changed
the name of the workgroup file.

 

[Project Orca]

After a bit more testing, I've been able to rule out some of the variables.
For now, discard the questions of network quirks, Active Directory
securities, and file locations. There is a set of 13 queries out of the 81
that she is never able to edit, regardless of where the database files are
located at. She's also unable to edit them when given explicit full
permissions to those query objects.

Here's the kicker though - the owner, sysadm, can edit the query, but it
doesn't have the ability to change the owner of the object to someone else.
The owners for the other 68 queries can be changed.

I have a gut feeling, based on the names of the queries, that these queries
may have been some of the original queries created in the database. I
thought that perhaps they were corrupted somehow in all of the upgrades and
such. I did a compact and repair on the database, but that didn't resolve
the issue.

Any ideas based on the new information?

 

[Joan Wild]

It sounds to me as though those queries were created while using a different mdw file. When you look at the owner of the query(ies), what does it say? (this shouldn't matter, except would explain why sysadm can't change the owner).

Are these queries RWOP? What kind of editing is she trying to do - i.e. adding tables/fields which she doesn't have permission to will fail, even though she has modify permission on the query.

Does this query have any lookup fields in it? Do any of the underlying tables have lookup fields defined?

--
Joan Wild
Microsoft Access MVP
: After a bit more testing, I've been able to rule out some of the variables.
: For now, discard the questions of network quirks, Active Directory
: securities, and file locations. There is a set of 13 queries out of the 81
: that she is never able to edit, regardless of where the database files are
: located at. She's also unable to edit them when given explicit full
: permissions to those query objects.
:
: Here's the kicker though - the owner, sysadm, can edit the query, but it
: doesn't have the ability to change the owner of the object to someone else.
: The owners for the other 68 queries can be changed.
:
: I have a gut feeling, based on the names of the queries, that these queries
: may have been some of the original queries created in the database. I
: thought that perhaps they were corrupted somehow in all of the upgrades and
: such. I did a compact and repair on the database, but that didn't resolve
: the issue.
:
: Any ideas based on the new information?
:
: "Project Orca" wrote:
:
: > Thanks for the heads-up on the file names! That fixed my second issue.
: >
: > I'm still scratching my head over the first issue though. She's not using
: > different databases - I've made sure that I'm using the same database and
: > workgroup file in my tests under her username. I test them on the mapped
: > drive and she's unable to edit a few specific queries (security on those
: > specific queries is identical to queries that she does have the ability to
: > edit). I copy the files (mdb and mdw) to the local PC, reopen Access, attach
: > to the local mdw file, close Access, reopen Access and open the local mdb
: > file and I can edit all queries.
: >
: > I checked the object dependencies for those queries, thinking that they may
: > be required for the reports that she couldn't edit (due to the mdb and mdw
: > file names being identical), but there was no common dependency among the
: > queries that she couldn't edit. The problem also continued after I changed
: > the name of the workgroup file.
: >
: > "Chris O'C via AccessMonster.com" wrote:
: >
: > > Since the permissions are applied to the db file and she has different
: > > permissions depending on where the file is, it means she's either opening the
: > > file as a different user or opening it with a different workgroup when she
: > > opens it on her PC. Or it means she's not opening an exact copy of the db
: > > file. (Maybe an older copy before she had permissions to these queries?)
: > >
: > > She doesn't have exclusive access to make changes to the db because someone
: > > accidentally named the workgroup file the same as the db file and placed both
: > > files in the same folder. DBName.mdw and DBName.mdb will both create a
: > > locking db file, DBName.ldb, when they open unless that file already exists.
: > > If DBName.ldb already exists, the db will be opened in shared mode, not
: > > exclusively.
: > >
: > > Either change the name of one of the files or move one of the files to
: > > another folder.
: > >
: > > Chris
: > > Microsoft MVP
: > >
: > >
: > > Project Orca wrote:
: > > >The user is attempting to change some queries on a database stored on a
: > > >server and accessed by a mapped drive, but gets the following error message
: > > >when trying to save the changes: "You do not have the necessary permissions
: > > >to use the '<query>' object. Have your administrator or the person who
: > > >created this object establish the appropriate permissions for you." (replace
: > > >the <query> with the name of the query currently being edited). There are
: > > >about 80 queries in the database, and I haven't been able to find a pattern
: > > >for which queries she is able to edit and which ones she is not.
: > > >
: > > >If she copies or moves the database to her local machine, she's able to make
: > > >changes to any of the queries.
: > > >
: > > >Regarding the security, the database was converted from an Access 97
: > > >database with no security to an Access 2000 (when connected to a workgroup
: > > >file - database.mdw as user "sysadm" ) and then to Access 2003 format (again
: > > >with the database.mdw and user "sysadm"). At that point, all of the objects
: > > >in the database had an owner of "sysadm".
: > > >
: > > >Then the security permissions were set on the database. The Users group was
: > > >only given permission. Another group was created for basic read/write
: > > >(GroupRW). The Admins group was given full permissions. The Admin user was
: > > >removed from the Admins group. Sysadm was already in the Admins group, the
: > > >GroupRW and Users group. User1 had the same groups (Admins, GroupRW, and
: > > >Users).
: > > >
: > > >The user has full control of the folder on the server where the database is
: > > >stored, as well as propagating that full control to the database files (mdb,
: > > >ldb, and mdw).
: > > >
: > > >The way I understand the security as it is set up, even though the
: > > >individual users don't have permissions to database objects, they should
: > > >still get the permissions that were assigned to their group - in this case
: > > >full permissions for User1 since she is part of the Admins group and the
: > > >Admins group has full permissions to all database objects.
: > > >
: > > >Can anyone see a reason why she wouldn't be allowed to edit a query? Can
: > > >anyone see a reason why she can edit some queries but not others (when they
: > > >all have the same security structure)?
: > > >
: > > >I don't want to muddy the waters but it may be important to note that there
: > > >35 forms, 45 reports, and 2 macros. She's unable to gain exclusive access to
: > > >change reports and forms when the database is located on the server - even if
: > > >she's the only one in the database at the time and the server reports no
: > > >other users with the database open. This is another issue that I need to
: > > >resolve, but I'm trying to tackle one area at a time...
: > >
: > > --
: >
: > >
: > >
: > >

 

[Project Orca]

When I look at the owner, it says that it is sysadm. I don't know about the
"RWOP".., but your question about the lookup fields was close to the answer.
I found that I was able to delete the queries, so I began looking for a way
to recreate them (hopefully resetting the security information) if possible.
I switched to SQL view (thinking that it would be easy to copy and paste the
SQL text into a newly created Query with the same name) and found that every
one of them had one thing in common: the last line of the SQL statement read
"WITH OWNERACCESS OPTION;". The other Queries in the database did not have
that line. I removed that line and she was able to modify the Queries.

Although the question is resolved (thanks everyone!), I'm still a bit
curious. Would Access be creating a new lock on the Query for the "owner"
when someone else attempted to modify the object and then choking because it
thinks that two people were trying to modify the query at the same time?

 

[Joan Wild]

When I look at the owner, it says that it is sysadm. I don't know about
the
"RWOP".., but your question about the lookup fields was close to the
answer.
I found that I was able to delete the queries, so I began looking for a
way
to recreate them (hopefully resetting the security information) if
possible.
I switched to SQL view (thinking that it would be easy to copy and paste
the
SQL text into a newly created Query with the same name) and found that
every
one of them had one thing in common: the last line of the SQL statement
read
"WITH OWNERACCESS OPTION;".

Those are RWOP queries (RWOP = run with owner permissions = WITH OWNERACCESS
OPTION).

Although the question is resolved (thanks everyone!), I'm still a bit
curious. Would Access be creating a new lock on the Query for the "owner"
when someone else attempted to modify the object and then choking because
it
thinks that two people were trying to modify the query at the same time?

No, but queries should be in the frontend, and each user have their own copy
of the frontend. More on splitting at

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
http://granite.ab.ca/access/splitapp/index.htm

Also, is your user modifying the query SQL property in code? If so, RWOP
won't work.