How do I revoke a SELECT permission on one column of a table and still allow the user to do a SELECT * FROM the table?

Question

From this answer, you can GRANT/REVOKE access to a specific column: https://dba.stackexchange.com/a/98626/141819

GRANT SELECT ON <schema>.<tablename>(<Col1>,<Col2>) TO [Domain\User]

However, when I do this it gives the user an error when they try to do a SELECT * that says that they don't have access to the column. Also, it makes Power BI throw an error saying the same thing. Is it possible to revoke access and make it so that when they do SELECT *, it just shows them all the columns that they have access to?

Answer 1

Those two things are mutually exclusive. If a user runs a SELECT *, they'll get all the columns. Revoking column permissions does not magically change the way a query operates, and if the user lacks permission for this the whole query will fail.

Here's the good news: SELECT * is poor practice in the first place, and making people choose which columns they need is a good thing to do.

But if you really want to do this, you can make a view with the offending column omitted, and allow them to select from the view.