Restricting Access to View's in ADP

  • Thread starter Thread starter Drahala via AccessMonster.com
  • Start date Start date
D

Drahala via AccessMonster.com

My organization has a networked SQL database that we wish to allow users to
run adhoc queries/reports against by way of an Access Data Project.
Ultimately, we would like to have our users connect and only be able to
access views that have been previously created within the SQL database. They
would then create their own views base upon the established views, and create
reports based upon thier views. Our database administrator is unfamiliar
with MS Access products and not sure how to set this up. I have the
following questions:

1. Should permissions to the database objects be established on the SQL side,
the Access side or both?

2. Assuming that access to datbase objects are to be set on the Access side,
is there a way of hiding database objects, aside from denying access to the
database window?

3. If permissions are to be set on the SQL side, what can we do to deny
access to any database objects aside from the views we wish them to have
(this may be a question for a SQL forum).


Many thanks in advance,

James
 
See comments inline.


Drahala via AccessMonster.com said:
My organization has a networked SQL database that we wish to allow users
to
run adhoc queries/reports against by way of an Access Data Project.
Ultimately, we would like to have our users connect and only be able to
access views that have been previously created within the SQL database.
They
would then create their own views base upon the established views, and
create
reports based upon thier views. Our database administrator is unfamiliar
with MS Access products and not sure how to set this up. I have the
following questions:

1. Should permissions to the database objects be established on the SQL
side,
the Access side or both?
Click to expand...

On SQL Side. Actually, with ADP, there is no way to set user permisions to
SQL Server objects (table, view, SP, UDF...)

2. Assuming that access to datbase objects are to be set on the Access
side,
is there a way of hiding database objects, aside from denying access to
the
database window?
Click to expand...


See above.

3. If permissions are to be set on the SQL side, what can we do to deny
access to any database objects aside from the views we wish them to have
(this may be a question for a SQL forum).
Click to expand...

With SQL Server, you can easily set user permission on different SQL Server
objects. In your case, you can just give user "SELECT..." permission to
certain views (or SPs). Users can write their ad hoc SQL SELECT statement
against thoes views as their reports' data source. I would not allow all
users to create views on SQL Server, if I am DB Admin.
 
Back
Top