I created a custom security group in my database in MS Access 2002. When the
user opens one of the forms, a temp table should be built using the user's
ID. This works fine if I put the user in the built-in Full Data Users group
but fails with my custom group with the resultant message of:

Could not create; no modify design permission for table or query
'UsernameTmpDetails'.

Using the User and Group Permissions tool, I've matched the security for
both groups on all objects, but I still get the error when using my custom
group. I'm confused by the note in the FAQ under section 15. Are they
saying that I can only set the create tables permission through the Security
Wizard? Thanks for any assistance.

gt

"GT" <(E-Mail Removed)> wrote in message
news:78D9E92E-EF7B-4821-978E-(E-Mail Removed)...
>I created a custom security group in my database in MS Access 2002. When
>the
> user opens one of the forms, a temp table should be built using the user's
> ID. This works fine if I put the user in the built-in Full Data Users
> group
> but fails with my custom group with the resultant message of:
>
> Could not create; no modify design permission for table or query
> 'UsernameTmpDetails'.

Is that the name of your query or temp table?
Since it works if they are also a member of the Full Data Users group, is
that OK, or should they not be a member of this group as well?

> Using the User and Group Permissions tool, I've matched the security for
> both groups on all objects, but I still get the error when using my custom
> group. I'm confused by the note in the FAQ under section 15. Are they
> saying that I can only set the create tables permission through the
> Security
> Wizard? Thanks for any assistance.

No, that section isn't relevant to your issue.


--
Joan Wild
Microsoft Access MVP

Effectively, yes, that is the table name. The query is done through code
with a DoCmd.RunSQL statement. The actual table name changes depending on
the username logged in. So, if Smith is logged in, the table name is
SmithTmpDetails.

As for using Full Data Users, I can get away with using it at least for now,
but eventually I don't everyone having access to all of the same objects.
Can you think of a reason why my custom group might not work in lieu of the
Full Data Users? Might have to set permission using code? I shouldn't think
so. Thanks.

gt

"Joan Wild" wrote:

> "GT" <(E-Mail Removed)> wrote in message
> news:78D9E92E-EF7B-4821-978E-(E-Mail Removed)...
> >I created a custom security group in my database in MS Access 2002. When
> >the
> > user opens one of the forms, a temp table should be built using the user's
> > ID. This works fine if I put the user in the built-in Full Data Users
> > group
> > but fails with my custom group with the resultant message of:
> >
> > Could not create; no modify design permission for table or query
> > 'UsernameTmpDetails'.
>
> Is that the name of your query or temp table?
> Since it works if they are also a member of the Full Data Users group, is
> that OK, or should they not be a member of this group as well?
>
> > Using the User and Group Permissions tool, I've matched the security for
> > both groups on all objects, but I still get the error when using my custom
> > group. I'm confused by the note in the FAQ under section 15. Are they
> > saying that I can only set the create tables permission through the
> > Security
> > Wizard? Thanks for any assistance.
>
> No, that section isn't relevant to your issue.
>
>
> --
> Joan Wild
> Microsoft Access MVP
>
>
>

"GT" <(E-Mail Removed)> wrote in message
news:3DBE3EAC-9F27-484B-822F-(E-Mail Removed)...
> Effectively, yes, that is the table name. The query is done through code
> with a DoCmd.RunSQL statement. The actual table name changes depending on
> the username logged in. So, if Smith is logged in, the table name is
> SmithTmpDetails.
>
> As for using Full Data Users, I can get away with using it at least for
> now,
> but eventually I don't everyone having access to all of the same objects.
> Can you think of a reason why my custom group might not work in lieu of
> the
> Full Data Users? Might have to set permission using code? I shouldn't
> think
> so. Thanks.


Yes, there is a bug with the security wizard, insofar as you then want to
create custom groups. If you stick with the wizard created groups, you are
OK. But any groups you create after the fact have problems. One is that
members of the custom group cannot save any queries, and it appears, as
though you have found another issue - they can't save tables.

The workaround is to include one the wizard generated groups (which isn't
always acceptable), or don't use the wizard at all. If you manually set up
security, creating any groups you like, you won't hit this problem. I
suppose that is an option for you. Unsecure it, and resecure it, but don't
let the wizard touch it.

The wizard should not be used by anyone who wants to create custom groups
other than those provided by the wizard.

--
Joan Wild
Microsoft Access MVP

"Joan Wild" wrote:

> "GT" <(E-Mail Removed)> wrote in message
> news:3DBE3EAC-9F27-484B-822F-(E-Mail Removed)...
> > Effectively, yes, that is the table name. The query is done through code
> > with a DoCmd.RunSQL statement. The actual table name changes depending on
> > the username logged in. So, if Smith is logged in, the table name is
> > SmithTmpDetails.
> >
> > As for using Full Data Users, I can get away with using it at least for
> > now,
> > but eventually I don't everyone having access to all of the same objects.
> > Can you think of a reason why my custom group might not work in lieu of
> > the
> > Full Data Users? Might have to set permission using code? I shouldn't
> > think
> > so. Thanks.
>
>
> Yes, there is a bug with the security wizard, insofar as you then want to
> create custom groups. If you stick with the wizard created groups, you are
> OK. But any groups you create after the fact have problems. One is that
> members of the custom group cannot save any queries, and it appears, as
> though you have found another issue - they can't save tables.
>
> The workaround is to include one the wizard generated groups (which isn't
> always acceptable), or don't use the wizard at all. If you manually set up
> security, creating any groups you like, you won't hit this problem. I
> suppose that is an option for you. Unsecure it, and resecure it, but don't
> let the wizard touch it.
>
> The wizard should not be used by anyone who wants to create custom groups
> other than those provided by the wizard.
>
> --
> Joan Wild
> Microsoft Access MVP
>
Joan,

Your response intrigued me. I thought I that when I used the Wizard that I
initially excluded all of the built-in groups and added the Full Data Users
group later using the Wizard. Not being sure, I decided to test my theory.
Using a previously unsecured copy of the database, I followed the FAQ steps
for securing my DB. I did not add in any of the built-in groups. I setup a
test ID and then built test group with all of the necessary privileges to all
objects. I assigned the test ID to the test group. Logged in to the DB with
my test ID and boom, it failed when attempting to build the table!

So, to get my custom group to work, I rebuilt my database. What I did
different from the FAQ is NOT to use the Wizard at all. When I got to step 7
of section 1 in the FAQ, I opened a blank database and imported all of my
objects and data from an unsecured version of my original DB and saved the
new DB with a new name. From there, I used the Users and Group Accounts
options to build my new users and custom group. Next, I used the User and
Group Permissions options to manually set the appropriate privileges to my
custom group and remove the User group's privileges. That was it. It now
works. By building the DB fresh while joined to the new security group, the
DB is apparently permanently associated with the new security group (that
might be a no brainer to the experts, but it was news to me). If you attempt
to open the DB while joined to the default MDW, you can't open it.
Encryption was not necessary but still an option and I'm sure recommended.
Of course, since the Wizard was not used, I had to manually construct the
shortcut to open the DB with the right MDW file.

Thanks for your help. This has been quite a learning experience. Again,
this was all with MS Access 2002.

GT

Joan, I just re-read your reply. I missed a point the first time. In the
end, I essentially did what you recommended. Thanks again.

"GT" wrote:

> "Joan Wild" wrote:
>
> > "GT" <(E-Mail Removed)> wrote in message
> > news:3DBE3EAC-9F27-484B-822F-(E-Mail Removed)...
> > > Effectively, yes, that is the table name. The query is done through code
> > > with a DoCmd.RunSQL statement. The actual table name changes depending on
> > > the username logged in. So, if Smith is logged in, the table name is
> > > SmithTmpDetails.
> > >
> > > As for using Full Data Users, I can get away with using it at least for
> > > now,
> > > but eventually I don't everyone having access to all of the same objects.
> > > Can you think of a reason why my custom group might not work in lieu of
> > > the
> > > Full Data Users? Might have to set permission using code? I shouldn't
> > > think
> > > so. Thanks.
> >
> >
> > Yes, there is a bug with the security wizard, insofar as you then want to
> > create custom groups. If you stick with the wizard created groups, you are
> > OK. But any groups you create after the fact have problems. One is that
> > members of the custom group cannot save any queries, and it appears, as
> > though you have found another issue - they can't save tables.
> >
> > The workaround is to include one the wizard generated groups (which isn't
> > always acceptable), or don't use the wizard at all. If you manually set up
> > security, creating any groups you like, you won't hit this problem. I
> > suppose that is an option for you. Unsecure it, and resecure it, but don't
> > let the wizard touch it.
> >
> > The wizard should not be used by anyone who wants to create custom groups
> > other than those provided by the wizard.
> >
> > --
> > Joan Wild
> > Microsoft Access MVP
> >
> Joan,
>
> Your response intrigued me. I thought I that when I used the Wizard that I
> initially excluded all of the built-in groups and added the Full Data Users
> group later using the Wizard. Not being sure, I decided to test my theory.
> Using a previously unsecured copy of the database, I followed the FAQ steps
> for securing my DB. I did not add in any of the built-in groups. I setup a
> test ID and then built test group with all of the necessary privileges to all
> objects. I assigned the test ID to the test group. Logged in to the DB with
> my test ID and boom, it failed when attempting to build the table!
>
> So, to get my custom group to work, I rebuilt my database. What I did
> different from the FAQ is NOT to use the Wizard at all. When I got to step 7
> of section 1 in the FAQ, I opened a blank database and imported all of my
> objects and data from an unsecured version of my original DB and saved the
> new DB with a new name. From there, I used the Users and Group Accounts
> options to build my new users and custom group. Next, I used the User and
> Group Permissions options to manually set the appropriate privileges to my
> custom group and remove the User group's privileges. That was it. It now
> works. By building the DB fresh while joined to the new security group, the
> DB is apparently permanently associated with the new security group (that
> might be a no brainer to the experts, but it was news to me). If you attempt
> to open the DB while joined to the default MDW, you can't open it.
> Encryption was not necessary but still an option and I'm sure recommended.
> Of course, since the Wizard was not used, I had to manually construct the
> shortcut to open the DB with the right MDW file.
>
> Thanks for your help. This has been quite a learning experience. Again,
> this was all with MS Access 2002.
>
> GT
>

"GT" <(E-Mail Removed)> wrote in message
newsAA22D34-BC0B-491E-8FC2-(E-Mail Removed)...

> By building the DB fresh while joined to the new security group, the
> DB is apparently permanently associated with the new security group (that
> might be a no brainer to the experts, but it was news to me). If you
> attempt
> to open the DB while joined to the default MDW, you can't open it.

Not quite. The db is not permanently associated with a mdw. You can in
fact create another mdw that could be used with this mdb. It is good that
the default mdw doesn't let you open the database.

The Access session is tied to the mdw.

Glad you got things working.


--
Joan Wild
Microsoft Access MVP