MS Query, when connecting to SQL Server through an ODBC Data Source, uses the system procedure "sp_tables" to retrieve a listing of tables in the database. The source for sp_tables can be viewed but not altered. The problem with this procedure is that it will not return a table name for which the connected user has permissions to access only certain columns. It only returns tables for which the connected user has full SELECT access.
We are using database roles with explicitly granted access to tables in our data warehouse. We have a model for building security down to the field level, but cannot enable it due to the problem above. Most users are accessing through MS Query and Excel, because everyone has MS Office and we have trained them in its use.
Since many reports have already been built around this product, migrating users to another product is not an option at this time.
One suggestion has been to create views with only the available columns and grant select on those views. This would work, but 1) would defeat the purpose of field-level access and isn't too far off of our current "work-around" and 2) would require retraining the users on using the new views instead of the tables they are used to.