Changing all Queries to RWOP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on implementing security into a database that is going to go
from a single user to a shared system and I going through the process of
setting up the security correctly (using the FAQ as my quide). From what I
have read about securing the tables properly I need to set all of my queries
run permissions to Owners instead of Users.

Q1. Is there a way to do this with code (I have about 900 queries) and I
would like to find a faster way than manually opening each query and changing
it.

Q2. This may be more appropriate for the security section of the group, but
for queries that run from command buttons where the query is an SQL statement
in the VB code, is there a way to make this run with owners permissions or do
I need to make all of these queries into stored objects.

Thanks for any help and advice on this

Jim
 
Q1. Is there a way to do this with code (I have about 900 queries) and
I would like to find a faster way than manually opening each query and
changing it.
Click to expand...

for each qdf in Querydefs
' shave off the final semicolon
temp = Left(qdf.SQL, Len(qdf.SQL)-1)

' add the RWOP clause
temp = temp & vbNewline & "WITH OWNERACCESS OPTION;"

' save it back
qdf.SQL = temp

next qdf
Q2. This may be more appropriate for the security section of the
group, but for queries that run from command buttons where the query
is an SQL statement in the VB code, is there a way to make this run
with owners permissions or do I need to make all of these queries into
stored objects.
Click to expand...

No: if the query is created in VBA code, its owner will be the user, not
you. What you can do is change the inline query to reference your RWOP
queries instead.

Hope it helps


Tim F
 
Thanks for the info. On the first part, when I try to run the code I get an
"error 13 type mismatch". Is there something else I need to do or any
references I need to set.

Thanks again

Jim
 
Back
Top