Search code examples
sqlsql-serveraccess-denied

Ignore denied columns in "select * from"

I need to restrict an (support) user from viewing columns in a table (other users should have full access to this table). So I granted access to only the columns I specified via "GRANT SELECT ON dbo.TestTable (FirstCol, SecondCol, ThirdCol) TO HR_Intern;"

But when I am running a "SELECT * FROM dbo.TestTable;" i got an Access Denied Error for every other column in the table. The user is doing customer support using the MSSQL Management Studio directly on the database and the errors won't allow the user to edit the data.

Is it possible to just display the columns the user have access to and ignoring every denied column?

Thanks for your help :)

Solution

  • Better to create a VIEW and provide the users access to it. In the VIEW only those columns these users can see should be part of SELECT statement.