Restricting Access to View's in ADP

[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

 

[Norman Yuan]

See comments inline.

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?

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?

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).

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.