It is certainly no easy task, but basically you want to create Views that restrict the rows that are returned, such as the following:

--SQL Server example

CREATE VIEW vw_MySales

AS

SELECT * FROM MySales WHERE TerritoryID IN (SELECT TerritoryID FROM AppUsers WHERE UserName = SUSER_SNAME())

This selects rows from the MySales records where the TerritoryID is in a list of territories (probably just one, since this is for a specific user, the one he is assigned to) that are returned from another select statement. This is if you want to show all records for a whole territory for the same territory the salesman (user) is assigned to, assuming he can see other salesmen's records in the same territory. You get the picture.

You can also create other views that restrict values, then use that view in the inside SELECT statement. This nesting gets very complicated very quickly. I had to do it this way, and it was a bear.

SPROCs are easier, because you can do more lookups and use other T-SQL code, a lot more code than VIEWs allow.

The key is denying their access to the underlying tables, and only allowing their use of the VIEWs and SPROCs.

This use of Table permissions would be very difficult in Access, much easier in SQL Server. You could upsize it in SQL Server, keeping an Access front-end.

Hope this helps more than confuses.....