Object permissions

13 Dec 2006 12:18 AM

Scott

Hi, I am running Access 2003 SP1, I have set up a "secured" Database with
multiple users and multiple permissions.
In one instance, I have code that modifies a query:
qdfABC.sql = strSQL
The user group: Admins has modify permissions on this query, but when the
code runs, it says "you do not have the necessary permissions to use the
'qryABC' object. Have your system administrator or the person who created
this object establish the appropriate permissions for you."
I then gave this group total permissions for this object i.e. 'Administer'
permissions. but still get the same error message.
The only one who can run this code successfully is me and the only thing I
can see that's different is that I'm the owner of the object.
So I tried to change the object's owner to the group "Admins" and then it
tells me that I don't have permissions to change the object's owner?!?!
Hello, I am the owner of the object and I have given my user name explicit
full rights to this object! As well as the admins group of which I too am a
member.
Am I missing something here?
Thanks in advance.

18 Dec 2006 3:42 AM

'69 Camaro

Hi, Scott.

> So I tried to change the object's owner to the group "Admins"
.. . .
> Am I missing something here?

Yes. You may assign one -- and only one -- user as the new owner of the
object. A group is not a user. A group contains zero to many users as
members, so a group is related conceptually to users, but a group can't
substitute for a user as the "owner."

> The user group: Admins has modify permissions on this query, but when the
> code runs, it says "you do not have the necessary permissions to use the
> 'qryABC' object.

Change the query to "Run With Owner Permissions" and ensure the query owner
has enough permissions on the underlying tables to do whatever the query is
doing (updating, adding, et cetera).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Show quoteHide quote

"Scott" wrote:

> Hi, I am running Access 2003 SP1, I have set up a "secured" Database with
> multiple users and multiple permissions.
> In one instance, I have code that modifies a query:
> qdfABC.sql = strSQL
> The user group: Admins has modify permissions on this query, but when the
> code runs, it says "you do not have the necessary permissions to use the
> 'qryABC' object. Have your system administrator or the person who created
> this object establish the appropriate permissions for you."
> I then gave this group total permissions for this object i.e. 'Administer'
> permissions. but still get the same error message.
> The only one who can run this code successfully is me and the only thing I
> can see that's different is that I'm the owner of the object.
> So I tried to change the object's owner to the group "Admins" and then it
> tells me that I don't have permissions to change the object's owner?!?!
> Hello, I am the owner of the object and I have given my user name explicit
> full rights to this object! As well as the admins group of which I too am a
> member.
> Am I missing something here?
> Thanks in advance.

18 Dec 2006 3:37 PM

Joan Wild

'69 Camaro wrote:

>> So I tried to change the object's owner to the group "Admins"
> . . .
>> Am I missing something here?
>
> Yes. You may assign one -- and only one -- user as the new owner of
> the object. A group is not a user. A group contains zero to many
> users as members, so a group is related conceptually to users, but a
> group can't substitute for a user as the "owner."

You can, and I have, made a group the owner of an object. Doesn't matter in
Scott's case, but I thought I'd point out that this can be done.

--
Joan Wild
Microsoft Access MVP

18 Dec 2006 8:43 PM

'69 Camaro

Hi, Joan.

> You can, and I have, made a group the owner of an object.

Doh! Thanks for the correction. I could have sworn I've done the same thing as
Scott and received the same error message because I accidentally grabbed the
group name, not the user name when I assigned the new owner. I can see that
there's no such problem when I try it now.

Thanks.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Show quoteHide quote

"Joan Wild" <jwild@nospamtyenet.com> wrote in message
news:upJGqprIHHA.5104@TK2MSFTNGP06.phx.gbl...
> '69 Camaro wrote:
>>> So I tried to change the object's owner to the group "Admins"
>> . . .
>>> Am I missing something here?
>>
>> Yes. You may assign one -- and only one -- user as the new owner of
>> the object. A group is not a user. A group contains zero to many
>> users as members, so a group is related conceptually to users, but a
>> group can't substitute for a user as the "owner."
>
> You can, and I have, made a group the owner of an object. Doesn't matter in
> Scott's case, but I thought I'd point out that this can be done.
>
> --
> Joan Wild
> Microsoft Access MVP
>

20 Dec 2006 6:20 PM

Scott

Thank you for the replies, but it really doesn't matter whether I'm trying to
change the owner to a group or an individual, it still says that I don not
have the permission to change the object's owner....but I am the owner of
that object, so I should have the permission to change it. Also, I do set the
querie's run permissions to 'owners' in the sql statement each time the code
runs, but it isn't that the person running the code doesn't have permission
to run the query, they can actually open the query once the sql is set. They
just can't set the sql itself. Below is the code used to set the sql for the
query:

'set query for attendance logs
strSQLAttendance = _
            "SELECT tblPayroll.CompNo, Month([Date]) AS [Month], Day([Date])
AS [Day], " & _
                    "tblPayroll.Date, Sum(tblPayrollDetails.Hrs) AS
SumOfHrs, " & _
                    "tblEmployees.EmployeeLastName,
tblEmployees.EmployeeFirstName, " & _
                    "tblPayroll.Reason, First(tblPayroll.HrsMissed) AS
FirstOfHrsMissed, " & _
                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
            "FROM ((tblPayroll INNER JOIN tblEmployees ON tblPayroll.CompNo
= tblEmployees.CompNo) " & _
                    "LEFT JOIN tblPayrollDetails ON tblPayroll.PayID =
tblPayrollDetails.PayID) " & _
                    "INNER JOIN tblAttendanceReasonCodes ON
tblPayroll.Reason = " & _
                    "tblAttendanceReasonCodes.AttendanceReasonCodeID " & _
            "WHERE (((tblEmployees.EmployeeNo) =" & EmpNo & ") And
((Year([Date])) =" & _
                    varYear & ")) " & _
            "GROUP BY tblPayroll.CompNo, Month([Date]), Day([Date]),
tblPayroll.Date, " & _
                    "tblEmployees.EmployeeLastName,
tblEmployees.EmployeeFirstName, tblPayroll.Reason, " & _
                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
            "ORDER BY tblEmployees.EmployeeLastName,
tblEmployees.EmployeeFirstName " & _
            "WITH OWNERACCESS OPTION "
Set objQDF = CurrentDb().QueryDefs("qryAttendanceActual")
    objQDF.SQL = strSQLAttendance
    objQDF.Close

Show quoteHide quote

"'69 Camaro" wrote:

> Hi, Joan.
>
> > You can, and I have, made a group the owner of an object.
>
> Doh! Thanks for the correction. I could have sworn I've done the same thing as
> Scott and received the same error message because I accidentally grabbed the
> group name, not the user name when I assigned the new owner. I can see that
> there's no such problem when I try it now.
>
> Thanks.
> Gunny
>
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
>
>
> "Joan Wild" <jwild@nospamtyenet.com> wrote in message
> news:upJGqprIHHA.5104@TK2MSFTNGP06.phx.gbl...
> > '69 Camaro wrote:
> >>> So I tried to change the object's owner to the group "Admins"
> >> . . .
> >>> Am I missing something here?
> >>
> >> Yes. You may assign one -- and only one -- user as the new owner of
> >> the object. A group is not a user. A group contains zero to many
> >> users as members, so a group is related conceptually to users, but a
> >> group can't substitute for a user as the "owner."
> >
> > You can, and I have, made a group the owner of an object. Doesn't matter in
> > Scott's case, but I thought I'd point out that this can be done.
> >
> > --
> > Joan Wild
> > Microsoft Access MVP
> >
>
>
>

20 Dec 2006 8:00 PM

Scott

Sorry, I accidentally posted before I was finished.

When the code executes, it stops at the line :
objQDF.SQL= strSQLAttendance with the error about not having necessary
permissions to use the query object even though this user has explicit and
implicit 'read and modify' permissions on the query.
If I set up a new query with this user or their group as the owner, then
this user can run the code with no problem. But I would prefer not to do
that. Especially when this code should work with only the modify permission
set anyhow.
Any help is appreciated

Show quoteHide quote

"Scott" wrote:

> Thank you for the replies, but it really doesn't matter whether I'm trying to
> change the owner to a group or an individual, it still says that I don not
> have the permission to change the object's owner....but I am the owner of
> that object, so I should have the permission to change it. Also, I do set the
> querie's run permissions to 'owners' in the sql statement each time the code
> runs, but it isn't that the person running the code doesn't have permission
> to run the query, they can actually open the query once the sql is set. They
> just can't set the sql itself. Below is the code used to set the sql for the
> query:
>
> 'set query for attendance logs
> strSQLAttendance = _
>             "SELECT tblPayroll.CompNo, Month([Date]) AS [Month], Day([Date])
> AS [Day], " & _
>                     "tblPayroll.Date, Sum(tblPayrollDetails.Hrs) AS
> SumOfHrs, " & _
>                     "tblEmployees.EmployeeLastName,
> tblEmployees.EmployeeFirstName, " & _
>                     "tblPayroll.Reason, First(tblPayroll.HrsMissed) AS
> FirstOfHrsMissed, " & _
>                     "tblAttendanceReasonCodes.AbsenceTypeID " & _
>             "FROM ((tblPayroll INNER JOIN tblEmployees ON tblPayroll.CompNo
> = tblEmployees.CompNo) " & _
>                     "LEFT JOIN tblPayrollDetails ON tblPayroll.PayID =
> tblPayrollDetails.PayID) " & _
>                     "INNER JOIN tblAttendanceReasonCodes ON
> tblPayroll.Reason = " & _
>                     "tblAttendanceReasonCodes.AttendanceReasonCodeID " & _
>             "WHERE (((tblEmployees.EmployeeNo) =" & EmpNo & ") And
> ((Year([Date])) =" & _
>                     varYear & ")) " & _
>             "GROUP BY tblPayroll.CompNo, Month([Date]), Day([Date]),
> tblPayroll.Date, " & _
>                     "tblEmployees.EmployeeLastName,
> tblEmployees.EmployeeFirstName, tblPayroll.Reason, " & _
>                     "tblAttendanceReasonCodes.AbsenceTypeID " & _
>             "ORDER BY tblEmployees.EmployeeLastName,
> tblEmployees.EmployeeFirstName " & _
>             "WITH OWNERACCESS OPTION "
> Set objQDF = CurrentDb().QueryDefs("qryAttendanceActual")
>     objQDF.SQL = strSQLAttendance
>     objQDF.Close
>
> "'69 Camaro" wrote:
>
> > Hi, Joan.
> >
> > > You can, and I have, made a group the owner of an object.
> >
> > Doh! Thanks for the correction. I could have sworn I've done the same thing as
> > Scott and received the same error message because I accidentally grabbed the
> > group name, not the user name when I assigned the new owner. I can see that
> > there's no such problem when I try it now.
> >
> > Thanks.
> > Gunny
> >
> > See http://www.QBuilt.com for all your database needs.
> > See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> > http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
> >
> >
> > "Joan Wild" <jwild@nospamtyenet.com> wrote in message
> > news:upJGqprIHHA.5104@TK2MSFTNGP06.phx.gbl...
> > > '69 Camaro wrote:
> > >>> So I tried to change the object's owner to the group "Admins"
> > >> . . .
> > >>> Am I missing something here?
> > >>
> > >> Yes. You may assign one -- and only one -- user as the new owner of
> > >> the object. A group is not a user. A group contains zero to many
> > >> users as members, so a group is related conceptually to users, but a
> > >> group can't substitute for a user as the "owner."
> > >
> > > You can, and I have, made a group the owner of an object. Doesn't matter in
> > > Scott's case, but I thought I'd point out that this can be done.
> > >
> > > --
> > > Joan Wild
> > > Microsoft Access MVP
> > >
> >
> >
> >

20 Dec 2006 8:03 PM

Joan Wild

Setting WITH OWNERACCESS OPTION in code is pointless, since the owner in
this case will the user running it and they may not (probably don't have)
permission on the underlying tables. You should be able to get it working if
you grant modify permission on the qryAttendanceActual query.

--
Joan Wild
Microsoft Access MVP

Scott wrote:

Show quoteHide quote

> Thank you for the replies, but it really doesn't matter whether I'm
> trying to change the owner to a group or an individual, it still says
> that I don not have the permission to change the object's
> owner....but I am the owner of that object, so I should have the
> permission to change it. Also, I do set the querie's run permissions
> to 'owners' in the sql statement each time the code runs, but it
> isn't that the person running the code doesn't have permission to run
> the query, they can actually open the query once the sql is set. They
> just can't set the sql itself. Below is the code used to set the sql
> for the query:
>
> 'set query for attendance logs
> strSQLAttendance = _
>            "SELECT tblPayroll.CompNo, Month([Date]) AS [Month],
> Day([Date]) AS [Day], " & _
>                    "tblPayroll.Date, Sum(tblPayrollDetails.Hrs) AS
> SumOfHrs, " & _
>                    "tblEmployees.EmployeeLastName,
> tblEmployees.EmployeeFirstName, " & _
>                    "tblPayroll.Reason, First(tblPayroll.HrsMissed) AS
> FirstOfHrsMissed, " & _
>                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
>            "FROM ((tblPayroll INNER JOIN tblEmployees ON
> tblPayroll.CompNo = tblEmployees.CompNo) " & _
>                    "LEFT JOIN tblPayrollDetails ON tblPayroll.PayID =
> tblPayrollDetails.PayID) " & _
>                    "INNER JOIN tblAttendanceReasonCodes ON
> tblPayroll.Reason = " & _
>                    "tblAttendanceReasonCodes.AttendanceReasonCodeID "
>            & _ "WHERE (((tblEmployees.EmployeeNo) =" & EmpNo & ") And
> ((Year([Date])) =" & _
>                    varYear & ")) " & _
>            "GROUP BY tblPayroll.CompNo, Month([Date]), Day([Date]),
> tblPayroll.Date, " & _
>                    "tblEmployees.EmployeeLastName,
> tblEmployees.EmployeeFirstName, tblPayroll.Reason, " & _
>                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
>            "ORDER BY tblEmployees.EmployeeLastName,
> tblEmployees.EmployeeFirstName " & _
>            "WITH OWNERACCESS OPTION "
> Set objQDF = CurrentDb().QueryDefs("qryAttendanceActual")
>    objQDF.SQL = strSQLAttendance
>    objQDF.Close
>
> "'69 Camaro" wrote:
>
>> Hi, Joan.
>>
>>> You can, and I have, made a group the owner of an object.
>>
>> Doh! Thanks for the correction. I could have sworn I've done the
>> same thing as Scott and received the same error message because I
>> accidentally grabbed the group name, not the user name when I
>> assigned the new owner. I can see that there's no such problem when
>> I try it now.
>>
>> Thanks.
>> Gunny
>>
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for
>> contact info.
>>
>>
>> "Joan Wild" <jwild@nospamtyenet.com> wrote in message
>> news:upJGqprIHHA.5104@TK2MSFTNGP06.phx.gbl...
>>> '69 Camaro wrote:
>>>>> So I tried to change the object's owner to the group "Admins"
>>>> . . .
>>>>> Am I missing something here?
>>>>
>>>> Yes. You may assign one -- and only one -- user as the new owner
>>>> of the object. A group is not a user. A group contains zero to
>>>> many users as members, so a group is related conceptually to
>>>> users, but a group can't substitute for a user as the "owner."
>>>
>>> You can, and I have, made a group the owner of an object. Doesn't
>>> matter in Scott's case, but I thought I'd point out that this can
>>> be done.
>>>
>>> --
>>> Joan Wild
>>> Microsoft Access MVP

20 Dec 2006 8:28 PM

Scott

Thanks Joan, but I have given the user explicit as well as implicit modify
rights on this query. Also, it is the only one that is giving me a problem. I
have others that work fine. That is the strange thing that has me puzzled. It
is set up the same as my other queries as far as permissions go. I know it's
hard to get a grasp of the entire picture without seeing all of the security
settings, but I agree with you....modify permissions should enable the user
to set the sql of the query at runtime but they don't in this case. I've
tried deleting the query and setting up another one but get the same error.
I noticed you said that the owner will be the user running the code. But
this isn't the case here. Would running code that modifies an object change
the object's owner?

Also, regardless of the code...shouldn't I (current owner of the query) be
able to pass ownership to another user?

Show quoteHide quote

"Joan Wild" wrote:

> Setting WITH OWNERACCESS OPTION in code is pointless, since the owner in
> this case will the user running it and they may not (probably don't have)
> permission on the underlying tables. You should be able to get it working if
> you grant modify permission on the qryAttendanceActual query.
>
> --
> Joan Wild
> Microsoft Access MVP
>
> Scott wrote:
> > Thank you for the replies, but it really doesn't matter whether I'm
> > trying to change the owner to a group or an individual, it still says
> > that I don not have the permission to change the object's
> > owner....but I am the owner of that object, so I should have the
> > permission to change it. Also, I do set the querie's run permissions
> > to 'owners' in the sql statement each time the code runs, but it
> > isn't that the person running the code doesn't have permission to run
> > the query, they can actually open the query once the sql is set. They
> > just can't set the sql itself. Below is the code used to set the sql
> > for the query:
> >
> > 'set query for attendance logs
> > strSQLAttendance = _
> >            "SELECT tblPayroll.CompNo, Month([Date]) AS [Month],
> > Day([Date]) AS [Day], " & _
> >                    "tblPayroll.Date, Sum(tblPayrollDetails.Hrs) AS
> > SumOfHrs, " & _
> >                    "tblEmployees.EmployeeLastName,
> > tblEmployees.EmployeeFirstName, " & _
> >                    "tblPayroll.Reason, First(tblPayroll.HrsMissed) AS
> > FirstOfHrsMissed, " & _
> >                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
> >            "FROM ((tblPayroll INNER JOIN tblEmployees ON
> > tblPayroll.CompNo = tblEmployees.CompNo) " & _
> >                    "LEFT JOIN tblPayrollDetails ON tblPayroll.PayID =
> > tblPayrollDetails.PayID) " & _
> >                    "INNER JOIN tblAttendanceReasonCodes ON
> > tblPayroll.Reason = " & _
> >                    "tblAttendanceReasonCodes.AttendanceReasonCodeID "
> >            & _ "WHERE (((tblEmployees.EmployeeNo) =" & EmpNo & ") And
> > ((Year([Date])) =" & _
> >                    varYear & ")) " & _
> >            "GROUP BY tblPayroll.CompNo, Month([Date]), Day([Date]),
> > tblPayroll.Date, " & _
> >                    "tblEmployees.EmployeeLastName,
> > tblEmployees.EmployeeFirstName, tblPayroll.Reason, " & _
> >                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
> >            "ORDER BY tblEmployees.EmployeeLastName,
> > tblEmployees.EmployeeFirstName " & _
> >            "WITH OWNERACCESS OPTION "
> > Set objQDF = CurrentDb().QueryDefs("qryAttendanceActual")
> >    objQDF.SQL = strSQLAttendance
> >    objQDF.Close
> >
> > "'69 Camaro" wrote:
> >
> >> Hi, Joan.
> >>
> >>> You can, and I have, made a group the owner of an object.
> >>
> >> Doh! Thanks for the correction. I could have sworn I've done the
> >> same thing as Scott and received the same error message because I
> >> accidentally grabbed the group name, not the user name when I
> >> assigned the new owner. I can see that there's no such problem when
> >> I try it now.
> >>
> >> Thanks.
> >> Gunny
> >>
> >> See http://www.QBuilt.com for all your database needs.
> >> See http://www.Access.QBuilt.com for Microsoft Access tips and
> >> tutorials.
> >> http://www.Access.QBuilt.com/html/expert_contributors2.html for
> >> contact info.
> >>
> >>
> >> "Joan Wild" <jwild@nospamtyenet.com> wrote in message
> >> news:upJGqprIHHA.5104@TK2MSFTNGP06.phx.gbl...
> >>> '69 Camaro wrote:
> >>>>> So I tried to change the object's owner to the group "Admins"
> >>>> . . .
> >>>>> Am I missing something here?
> >>>>
> >>>> Yes. You may assign one -- and only one -- user as the new owner
> >>>> of the object. A group is not a user. A group contains zero to
> >>>> many users as members, so a group is related conceptually to
> >>>> users, but a group can't substitute for a user as the "owner."
> >>>
> >>> You can, and I have, made a group the owner of an object. Doesn't
> >>> matter in Scott's case, but I thought I'd point out that this can
> >>> be done.
> >>>
> >>> --
> >>> Joan Wild
> >>> Microsoft Access MVP
>
>
>

20 Dec 2006 9:23 PM

Joan Wild

I've lost the original post here. Who is the owner of the query? What
permissions does the owner have on the underlying tables? What permissions
does the user have on the query; on the tables.

What is the error message you get when trying to run as the user?

--
Joan Wild
Microsoft Access MVP

Scott wrote:

Show quoteHide quote

> Thanks Joan, but I have given the user explicit as well as implicit
> modify rights on this query. Also, it is the only one that is giving
> me a problem. I have others that work fine. That is the strange thing
> that has me puzzled. It is set up the same as my other queries as far
> as permissions go. I know it's hard to get a grasp of the entire
> picture without seeing all of the security settings, but I agree with
> you....modify permissions should enable the user to set the sql of
> the query at runtime but they don't in this case. I've tried deleting
> the query and setting up another one but get the same error. I
> noticed you said that the owner will be the user running the code.
> But this isn't the case here. Would running code that modifies an
> object change the object's owner?
>
> Also, regardless of the code...shouldn't I (current owner of the
> query) be able to pass ownership to another user?
>
> "Joan Wild" wrote:
>
>> Setting WITH OWNERACCESS OPTION in code is pointless, since the
>> owner in this case will the user running it and they may not
>> (probably don't have) permission on the underlying tables. You
>> should be able to get it working if you grant modify permission on
>> the qryAttendanceActual query.
>>
>> --
>> Joan Wild
>> Microsoft Access MVP
>>
>> Scott wrote:
>>> Thank you for the replies, but it really doesn't matter whether I'm
>>> trying to change the owner to a group or an individual, it still
>>> says that I don not have the permission to change the object's
>>> owner....but I am the owner of that object, so I should have the
>>> permission to change it. Also, I do set the querie's run permissions
>>> to 'owners' in the sql statement each time the code runs, but it
>>> isn't that the person running the code doesn't have permission to
>>> run the query, they can actually open the query once the sql is
>>> set. They just can't set the sql itself. Below is the code used to
>>> set the sql for the query:
>>>
>>> 'set query for attendance logs
>>> strSQLAttendance = _
>>>            "SELECT tblPayroll.CompNo, Month([Date]) AS [Month],
>>> Day([Date]) AS [Day], " & _
>>>                    "tblPayroll.Date, Sum(tblPayrollDetails.Hrs) AS
>>> SumOfHrs, " & _
>>>                    "tblEmployees.EmployeeLastName,
>>> tblEmployees.EmployeeFirstName, " & _
>>>                    "tblPayroll.Reason, First(tblPayroll.HrsMissed)
>>> AS FirstOfHrsMissed, " & _
>>>                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
>>>            "FROM ((tblPayroll INNER JOIN tblEmployees ON
>>> tblPayroll.CompNo = tblEmployees.CompNo) " & _
>>>                    "LEFT JOIN tblPayrollDetails ON tblPayroll.PayID
>>> = tblPayrollDetails.PayID) " & _
>>>                    "INNER JOIN tblAttendanceReasonCodes ON
>>> tblPayroll.Reason = " & _
>>>                    "tblAttendanceReasonCodes.AttendanceReasonCodeID
>>>            " & _ "WHERE (((tblEmployees.EmployeeNo) =" & EmpNo & ")
>>> And ((Year([Date])) =" & _
>>>                    varYear & ")) " & _
>>>            "GROUP BY tblPayroll.CompNo, Month([Date]), Day([Date]),
>>> tblPayroll.Date, " & _
>>>                    "tblEmployees.EmployeeLastName,
>>> tblEmployees.EmployeeFirstName, tblPayroll.Reason, " & _
>>>                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
>>>            "ORDER BY tblEmployees.EmployeeLastName,
>>> tblEmployees.EmployeeFirstName " & _
>>>            "WITH OWNERACCESS OPTION "
>>> Set objQDF = CurrentDb().QueryDefs("qryAttendanceActual")
>>>    objQDF.SQL = strSQLAttendance
>>>    objQDF.Close
>>>
>>> "'69 Camaro" wrote:
>>>
>>>> Hi, Joan.
>>>>
>>>>> You can, and I have, made a group the owner of an object.
>>>>
>>>> Doh! Thanks for the correction. I could have sworn I've done the
>>>> same thing as Scott and received the same error message because I
>>>> accidentally grabbed the group name, not the user name when I
>>>> assigned the new owner. I can see that there's no such problem
>>>> when I try it now.
>>>>
>>>> Thanks.
>>>> Gunny
>>>>
>>>> See http://www.QBuilt.com for all your database needs.
>>>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>>>> tutorials.
>>>> http://www.Access.QBuilt.com/html/expert_contributors2.html for
>>>> contact info.
>>>>
>>>>
>>>> "Joan Wild" <jwild@nospamtyenet.com> wrote in message
>>>> news:upJGqprIHHA.5104@TK2MSFTNGP06.phx.gbl...
>>>>> '69 Camaro wrote:
>>>>>>> So I tried to change the object's owner to the group "Admins"
>>>>>> . . .
>>>>>>> Am I missing something here?
>>>>>>
>>>>>> Yes. You may assign one -- and only one -- user as the new owner
>>>>>> of the object. A group is not a user. A group contains zero to
>>>>>> many users as members, so a group is related conceptually to
>>>>>> users, but a group can't substitute for a user as the "owner."
>>>>>
>>>>> You can, and I have, made a group the owner of an object. Doesn't
>>>>> matter in Scott's case, but I thought I'd point out that this can
>>>>> be done.
>>>>>
>>>>> --
>>>>> Joan Wild
>>>>> Microsoft Access MVP

20 Dec 2006 9:31 PM

Scott

I think I figured it out...with your help of course. What you said about
setting the RWOP in code got me thinking, and also reading in some other
posts. Evidently, only the owner can set the RWOP property on a query. Not a
user with modify rights. Once I removed that line from the sql, the code ran
successfully. Although I did have to give the group read permissions on the
underlying tables themselves. But this was not a problem as there isn't any
sensitive data stored in those tables, just attendance information.
Either way, thanks a million for your time in steering me in the right
direction!
Scott

Show quoteHide quote

"Joan Wild" wrote:

> Setting WITH OWNERACCESS OPTION in code is pointless, since the owner in
> this case will the user running it and they may not (probably don't have)
> permission on the underlying tables. You should be able to get it working if
> you grant modify permission on the qryAttendanceActual query.
>
> --
> Joan Wild
> Microsoft Access MVP
>
> Scott wrote:
> > Thank you for the replies, but it really doesn't matter whether I'm
> > trying to change the owner to a group or an individual, it still says
> > that I don not have the permission to change the object's
> > owner....but I am the owner of that object, so I should have the
> > permission to change it. Also, I do set the querie's run permissions
> > to 'owners' in the sql statement each time the code runs, but it
> > isn't that the person running the code doesn't have permission to run
> > the query, they can actually open the query once the sql is set. They
> > just can't set the sql itself. Below is the code used to set the sql
> > for the query:
> >
> > 'set query for attendance logs
> > strSQLAttendance = _
> >            "SELECT tblPayroll.CompNo, Month([Date]) AS [Month],
> > Day([Date]) AS [Day], " & _
> >                    "tblPayroll.Date, Sum(tblPayrollDetails.Hrs) AS
> > SumOfHrs, " & _
> >                    "tblEmployees.EmployeeLastName,
> > tblEmployees.EmployeeFirstName, " & _
> >                    "tblPayroll.Reason, First(tblPayroll.HrsMissed) AS
> > FirstOfHrsMissed, " & _
> >                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
> >            "FROM ((tblPayroll INNER JOIN tblEmployees ON
> > tblPayroll.CompNo = tblEmployees.CompNo) " & _
> >                    "LEFT JOIN tblPayrollDetails ON tblPayroll.PayID =
> > tblPayrollDetails.PayID) " & _
> >                    "INNER JOIN tblAttendanceReasonCodes ON
> > tblPayroll.Reason = " & _
> >                    "tblAttendanceReasonCodes.AttendanceReasonCodeID "
> >            & _ "WHERE (((tblEmployees.EmployeeNo) =" & EmpNo & ") And
> > ((Year([Date])) =" & _
> >                    varYear & ")) " & _
> >            "GROUP BY tblPayroll.CompNo, Month([Date]), Day([Date]),
> > tblPayroll.Date, " & _
> >                    "tblEmployees.EmployeeLastName,
> > tblEmployees.EmployeeFirstName, tblPayroll.Reason, " & _
> >                    "tblAttendanceReasonCodes.AbsenceTypeID " & _
> >            "ORDER BY tblEmployees.EmployeeLastName,
> > tblEmployees.EmployeeFirstName " & _
> >            "WITH OWNERACCESS OPTION "
> > Set objQDF = CurrentDb().QueryDefs("qryAttendanceActual")
> >    objQDF.SQL = strSQLAttendance
> >    objQDF.Close
> >
> > "'69 Camaro" wrote:
> >
> >> Hi, Joan.
> >>
> >>> You can, and I have, made a group the owner of an object.
> >>
> >> Doh! Thanks for the correction. I could have sworn I've done the
> >> same thing as Scott and received the same error message because I
> >> accidentally grabbed the group name, not the user name when I
> >> assigned the new owner. I can see that there's no such problem when
> >> I try it now.
> >>
> >> Thanks.
> >> Gunny
> >>
> >> See http://www.QBuilt.com for all your database needs.
> >> See http://www.Access.QBuilt.com for Microsoft Access tips and
> >> tutorials.
> >> http://www.Access.QBuilt.com/html/expert_contributors2.html for
> >> contact info.
> >>
> >>
> >> "Joan Wild" <jwild@nospamtyenet.com> wrote in message
> >> news:upJGqprIHHA.5104@TK2MSFTNGP06.phx.gbl...
> >>> '69 Camaro wrote:
> >>>>> So I tried to change the object's owner to the group "Admins"
> >>>> . . .
> >>>>> Am I missing something here?
> >>>>
> >>>> Yes. You may assign one -- and only one -- user as the new owner
> >>>> of the object. A group is not a user. A group contains zero to
> >>>> many users as members, so a group is related conceptually to
> >>>> users, but a group can't substitute for a user as the "owner."
> >>>
> >>> You can, and I have, made a group the owner of an object. Doesn't
> >>> matter in Scott's case, but I thought I'd point out that this can
> >>> be done.
> >>>
> >>> --
> >>> Joan Wild
> >>> Microsoft Access MVP
>
>
>