Re: Group Permissions and SQL

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hallo "croy".

"croy" wrote:
Split, secured database. Everything working well, except...

In the After Update event for a control on a form, I have
this code:

Private Sub grpLocType_AfterUpdate()

Dim SQL As String
SQL = "DELETE qrysfrmLocPicker.GeoLocId " & _
"FROM tblLocPicker WITH OWNERACCESS OPTION"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Me![sfrmLocPickerObj].Form.Requery

End Sub

The SQL looks strange. Usually tblLocPicker would be the name of a
table but qrysfrmLocPicker would be the name of a query.

It works fine with the Admins group, and with another group
"Field-Admins". But it won't work with another group having
lesser permissions.

The error is "Run-time error '3112': Records cannot be
read; no read permissions on 'tblLocPicker'", and it leads
to the SQL statement above.

But this groups permissions *do* have permissions to do
anything but administer or modify that linked table. I've
gone so far as to give that group administer permission on
every table, every query, and every form, and it made no
difference. And the SQL refers to a saved query with "Run
with Owner's Permissions".

What am I missing?

The "owneraccess option" doesn't strike through to the backend db,
it only affects the "table link". You will either have to give the
users delete permissions on the backend table or use an IN clause
in the query that contains the path to the backend database:
DELETE * FROM tblLocPicker
IN 'C:\somepath\backend.mdb'
WITH OWNERACCESS OPTION
But wait: Who is the owner of a sql string in code? So you should
save the delete query in the database and execute it using
DoCmd.OpenQuery

--
Regards,
Wolfgang


.

Relevant Pages

  • Re: What commands can a user execute
    ... simple security query rather than BOL. ... example would be the command UPDATE STATISTICS. ... ALTER permissions on the table or view. ... you could retrieve this with a query from SQL Server rather than Books ...
    (microsoft.public.sqlserver.security)
  • Re: Object permissions
    ... Who is the owner of the query? ... permissions does the owner have on the underlying tables? ... does the user have on the query; ... to 'owners' in the sql statement each time the code runs, ...
    (microsoft.public.access.security)
  • Re: Pass Through Query Question
    ... I did some more searching and found the answer from Joan Wild on the ... security group - The users need to have write permissions to the pass ... >I am using VBA code to update teh SQL of a pass through query to SQL ...
    (microsoft.public.access.modulesdaovba)
  • Re: Group Permissions and SQL
    ... With Owneraccess option in the SQL statement is pointless if qrysfrmLocPicker is a saved RWOP query ... Do the users have delete permissions on qrysfrmLocPicker? ...
    (microsoft.public.access.security)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)