I have 'R' rights on <table> from two roles. The first role gives me access to 41 of 57 columns. The second role gives me access to the other 16 columns. When I run "SELECT TOP 10 * FROM <table>" I get "3523: The user does not have SELECT access to <table>". If I replace "*" by enumerating the 57 columns, I get 10 rows.
Why won't "SELECT *" work? Or, what did my employer's DBAs choose that keeps "SELECT *" from working?
As you discovered, "SELECT *" looks for an access rights row for 'All' columns. The "*" is not expanded to a list of column names until after the access rights check.
Then how do I make an enhancement request that the expansion occurs prior to the access rights check? Given that I'm sure my employer is not the only firm that has migrated from other RDBMS (Oracle, DB2, SQL Server) that don't behave this way, my low expectations on this "feature" changing are probably appropriate.
Today I was told that the workaround is to generate a view with the same name in my personal schema that has only the columns I can access. Thankfully, we are provided with enough information that I can automate that....