User in Admin Group Can't Modify Objects

[croy]

Access 2002 under Windows XP, all patched.

Split db with user-level security applied and working well.

The front-end that I routinely pass around was getting a bit
bloated with a bunch of analysis tables and queries, so I
made a copy and renamed it. Then I copied it to the machine
of another user who is a member of the admins group.

When he opens the database with a modified shortcut and his
admin logon, and tries to modify a query, he gets a message
stating that Admin permissions are needed for that item to
be modified.

When I go to his machine, and log on with my owner/admin
account, I get the same problem.

Any ideas as to why this renamed copy of the often
distributed front-end would be refusing our desires?

The reason has to be simple, but apparently not simple
enough for me!

 

[Joan Wild]

It would seem to me that you are using a different mdw than the one that was
used to secure it with (perhaps a production copy of the mdw, vs. a
development mdw). The Admins Group would be different in these two.

 

[croy]

It would seem to me that you are using a different mdw than the one that was
used to secure it with (perhaps a production copy of the mdw, vs. a
development mdw). The Admins Group would be different in these two.

Thanks for the reply, Joan.

The mdw file is the same one we've been using since March,
and in October we were both building and modifying *lots* of
queries.

But sometime between October and now, something changed.
When I copy even our standard front-end to his drive, he
can't modify and save a query, *unless* (just found this out
today) he changes the Run Permissions to "User"--then he can
modify it and save it. But all the queries in these
front-ends have always had the owner's run permissions--so
why the change in behavior?

I can't think of what might have changed. My only
successful work-around, so far, has been to use my Fisher
Find and Replace to strip out all the "WITH OWNERACCESS
OPTION" from the queries, and copy over that version of the
analysis front-end.

Could any of the recent Windows Security Updates affect
this?

If anyone has any thoughts of things to try, I'm all ears!

 

[Joan Wild]

Are you changing the sql property of this query in code? Does the user have
modify permissions on the saved query?

Removing the RWOP should create more problems (i.e. permissions), not less.

 

[croy]

Are you changing the sql property of this query in code?

If you mean the Run With property, no. The other user is
doing it manually to each query (a pain for him).

If you mean something else, I'm not sure, except to say that
I'm not changing any query properties in code.

Does the user have modify permissions on the saved query?

Yes. The original admins group is still in place, and he's
included in that. He's also included in a custom "Field
admin" group, but that's probably unnecessary--as I
understand it, if someone is in the admins group, they're
gold. I experimented and gave him *explicit* permissions for
everything, but no improvement.

Removing the RWOP should create more problems (i.e. permissions), not less.

Yes, that's another oddity in this case.

The other user is out sick today, and right now I'm sitting
here pondering this: he said if he changes a query's
"Run-With" property to "User's", he can then save the
query.! Hmmm. What branch of the logic tree is this? It's
is making no sense to me. But... his work-around is now to
change the property, and then continue to modify and save
queries.

I *seem* to recall, that when I went to his machine and
opened the database with my database login, I was not able
to save query changes either, but it could be that what I'm
recalling is not being able to change the owner of the
queries, getting the message that I must have admin
permissions to do that--which I do, after all, I'm the
owner!

Ugh.

I'll have to wait now until the other user is back from
death, and play some more then.

 

[croy]

Are you changing the sql property of this query in code? Does the user have
modify permissions on the saved query?

Removing the RWOP should create more problems (i.e. permissions), not less.

I haven't been able to get time with the other user to do
more testing, so I tried these two tests on my own:

1. Created a new user, added "him" to the admins group, and
then logged into the db with the new user's login. Result:
not able to save query modifications!

2. Gave the new user explicit permissions to administer all
the object types, and then logged into the db with the new
user's login.. Result: not able to save query
modifications!

If I had managed to get crossed up on which was the correct
MDW file (development vs distribution), then I could see
that #1 would fail. But #2 should have worked, right?

I'm nearing the point of making copies of the front- and
back-end files and removing security altogether, and then
re-running the wizard, in the *hope* that this would fix it,
but any other thoughts would certainly be appreciated.

 

[Joan Wild]

Based on what you've told us, I have no other ideas.

Verify the owner of the query (does this user have full permissions on the
underlying tables?).
Verify the permissions on the query object.

 

[croy]

Based on what you've told us, I have no other ideas.

That's not surprising--this situation simply doesn't make
sense. I keep waiting for the "aha!" moment, but my hopes
are fading. But I sure appreciate your thoughts. You have
helped myself and so many others here for so long--you
deserve a medal!

Verify the owner of the query (does this user have full permissions on the
underlying tables?).

I am the owner, and I've given the other admin user implicit
and explicit permissions on everything, front- and back-end.

Verify the permissions on the query object.

I've checked all this several times, but this morning I plan
to do another run-thru on everything--from the shortcuts on
thru the accounts, and permissions. But it sure stumps me
that the simplest test--create a new user and assign full
admins permissions, then log on as that account--leaves the
new admin account unable to modify and save queries. And
that's right here on my machine! It sure smells like using
a deployment MDW that's different from the development MDW,
but when and how that change might have occurred, I have no
idea. It had to have happened between late September and
early December, as in late September, both of us were
furiously writing, modifying, and testing queries, and in
many cases, changing and saving each other's queries. And
all this done with the same front-end/back-end setup of me
simply copying the front-end to his machine whenever there
were changes.

Again, it looks like removing security completely, and then
rebuilding it again, is the next step (but a long one, with
no guarantees).

 

[Joan Wild]

It had to have happened between late September and
early December, as in late September, both of us were
furiously writing, modifying, and testing queries, and in
many cases, changing and saving each other's queries. And
all this done with the same front-end/back-end setup of me
simply copying the front-end to his machine whenever there
were changes.

Is it possible that some of this wasn't a copy of the mdb, but an import
from yours to his? Remember that permissions don't travel with an object
that is imported.

Good luck

 

[croy]

Is it possible that some of this wasn't a copy of the mdb, but an import
from yours to his?

Ummm... I think there were a few cases like that, for some
queries....

Remember that permissions don't travel with an object
that is imported.

But my understanding (or lack thereof) is that the importing
user becomes the owner of those items, and therefor, have
full permissions... no? I need to experiment with this.

 

[croy]

On Tue, 23 Dec 2008 11:08:17 -0500, "Joan Wild"

For what it's worth, I just tried to use the jetComp utility
on both the front- and back-end, and it wouldn't work on
either--it kept regurgitating the dialog for username and
password, ten or 11 times, before throwing up a dialog with,
"Error compacting database". There was a setting for this
utility that I don't understand: "Set system database
location". It defaults to, "System.mdb", and offers a
button to navigate to wherever you want it to be. I just
don't know what is meant by "System" database.

 

[croy]

Good luck

I made a copy of the front-end for experimenting, and
decided to change the ownership of all the queries from my
account to the other admin user's account.

But Access refused, saying that I need admin permissions to
do that (I'm not only in the admins group, I'm the Owner!).
That was trying from the standard menu item under
Tools|Security.

For reasons I no longer recall, I decided to try it from the
Security Manager add-in. It wouldn't let me change
ownership of the queries either, but it gave me a completely
different explanation:

*****
*****
Change Owner
*****
You cannot change the Owner of a query set to run with
Owner's Permissions.

The owner can be changed in Access using the following
steps:

1) The query's current owner, or an account with Modify
Design permission,
changes the Run Permissions to User's,

2) An account with Administer permission then assigns
ownership to another
user or group account, and

3) The new owner, or a member of the group owning the
query, changes the
Run Permissions back to Owner's.

-- OK --
*****

So I then used Fisher's Find and Replace add-in to get rid
of the "RunWithOwnersPermissions" from the tail end of the
SQL of *all* of the queries, and then I could change the
ownership to the other admin user (actually, a mock other
user's account, so that I can log on with it).

Now I can open that front-end using the mock other-user's
account, and modify/save queries without a problem.

Why an admins-level user can't save a query that has the
owner's run permissions and has been modified, I can't say,
but this *sort of* answers the question of why the other
user could modify and save queries after he changed the
RunWith permissions to "User's". Perhaps "RunWith" infers
"RunWith/Modify"?

Progress... maybe. This is either "as designed" in Access
2002, or there's some kind of corruption, bug, or ??? in
this database.

 

[Joan Wild]

The system meaning the mdw file.

 

[Joan Wild]

Access doesn't see you as being in the Admins Group - so you appear to be
using the wrong mdw at the time you attempt this (at least as far as Access
is concerned)

 

[croy]

The system meaning the mdw file.

Ahh. Thanks. That made the difference, as far as getting
it to run, but no difference with the db's whacko
permissions.

 

[croy]

Access doesn't see you as being in the Admins Group - so you appear to be
using the wrong mdw at the time you attempt this (at least as far as Access
is concerned)

But, the security add-in's message states that ownership
can't be changed for a query that is set to run with owner's
permissions. That lines up with what I'm experiencing. When
I flip the RunWith bit from "owner's" to "user's", I can
then do anything I want in the way of modify, save, change
ownership, etc. I can also modify and save any other object
(tables, forms, reports, etc.).

A couple of questions here, as I ponder some of the things I
thought I knew...

Am I wrong to think that if I were using the wrong mdw, that
I wouldn't be able to modify and save the querys at all,
even *if* I change the RunWith bit?

Also, am I wrong in thinking that if I import a query from
another mdb, that the ownership (in the receiving mdb)
should be mine, thereby giving me carte blanche permissions
on it?

Over the holidays, I'm going to pour over the FAQ again, and
hopefully find something I've missed, and reinforce where my
understanding is correct (although *that* may be a very
short list!).

Happy holidays, and belated winter solstice!

--
croy

"A baby in motion is a body at rest."

- author unknown

 

[Doc Kelly]

Not to sound stupid, but have you tried simply re-joining the user group?
Can you share the original mdw and join through the network?

 

[croy]

On Tue, 6 Jan 2009 00:01:01 -0800, Doc Kelly <Doc

Not to sound stupid...

I'm taking credit for any stupidity around here, mister...
(in my best John Wayne imitation). %-)

...but have you tried simply re-joining the user group?

Well, we are joining fresh at each launch of the front-end,
by using a shortcut that joins us to the user group. Er, is
that what you meant?

Can you share the original mdw and join through the network?

Yeah, that's what we're doing. The back-end and MDW are on
my machine, and he's using the MDW on my machine and the
front-end on his machine to get to the back-end on my
machine.

All the parts on my machine are on a FAT-32 drive. The
front-end on his machine is on a NTFS drive. Both machines
are WinXP/Office 2002. And I believe all patches are in
place.