limiting access to reports

[Guest]

In my employee database, supervisors will need the ability to run reports on their employees. This reporting can begin at any level of the organizational hierarchy. The Director will need to run reports on any of her supervisors and their employees; her supervisors will need to run reports on their employees; employees will need to run reports on themselves. Each employee record has a field which gives that person’s supervisor number.

I have the reports working for supervisors, but I haven’t figured out how to limit the supervisors report to only their employees. As it is right now, any db user can run a report on anyone. (This db has not been released and is still in the development stages.) Data and tables are on a SQL Server and users have the front end forms, reports, etc. We’re using SQL logon authentication for access to the tables.

Where can I find information about how to limit a user’s access to only their employee’s records? I know the answer lies in a select statement, but how do I identify the current logged on user?

tia,

 

[Joseph Meehan]

In my employee database, supervisors will need the ability to run
reports on their employees. This reporting can begin at any level of
the organizational hierarchy. The Director will need to run reports
on any of her supervisors and their employees; her supervisors will
need to run reports on their employees; employees will need to run
reports on themselves. Each employee record has a field which gives
that person's supervisor number.

I have the reports working for supervisors, but I haven't figured out
how to limit the supervisors report to only their employees. As it
is right now, any db user can run a report on anyone. (This db has
not been released and is still in the development stages.) Data and
tables are on a SQL Server and users have the front end forms,
reports, etc. We're using SQL logon authentication for access to the
tables.

Where can I find information about how to limit a user's access to
only their employee's records? I know the answer lies in a select
statement, but how do I identify the current logged on user?

tia,

I suggest that first, if you have not already, you need to study up on
user level security and split databases.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can lock
everyone including God out.

Practice on some copies to make sure you know what you are doing.

Next I suggest that you set the criteria in a query or report to limit
the results to the employees they should include. Of course you need a
table or other source for the query to know which employees. If the
employees never change or can be identified by some characteristic like
department you can hard code that if you like.