I don't think the permissions from Sage apply to the actual data. In Sage 100 Advanced, there is an "ODBC Security" option however the help file states that this is NOT available for Sage 100 Premium.

Reports run from Sage for Premium use the MAS_Reports SQL account, which absolutely 100% requires full read access to all Sage 100 data.  This means you have to be especially wary of custom report permissions because any report run from Sage has no ODBC restrictions. 

Otherwise you are managing SQL permissions on your own (for external queries / ODBC).  Create custom SQL Roles (Google has instructions) and assign them to users.  Do not in any way change the SQL permissions for MAS_User / MAS_Reports.

Thank you both, that was exactly what I needed to know!

So I understand now that I'd have to use SQL Server permissions to grant users access to their databases, but to expand on the idea that I don't want our AP users to be able to query the Payroll tables and I don't want the Payroll users to be able to query the AP tables, is there an easy way to accomplish that? Or would that have to be done on a table-by-table basis?

Create SQL Roles for AP_ReadOnly and PR_ReadOnly with Select access to the applicable tables, then give the roles to the appropriate users.

Script the role creation because they are created in the database, not the server.

Note: some PR data (PII) is unavailable for ODBC, and can only be seen through Sage 100 custom reports.

Got it, thanks again!