Once I was thrown into an interesting situation where a client told me that he can see the data from the table but not the table. I asked him to elaborate more and he sent me below screenshots where he was unable to see the table in SSMS.
Below we can see that Sysadmin level account is connected to SQL Server and it can see database foo and also Table_1 in the database.
Below is from a non-sysadmin user who is not able to see the database in object explorer BUT when he queries the table Table_1 in foo database, it works fine.
I took a little time to capture profile when opening management studio. It didn’t take much time to realize that there is somewhere we have DENY permission applied. I have asked to share the output of below query.
USE master GO SELECT class ,major_id ,grantee_principal_id ,permission_name ,state_desc FROM sys.server_permissions WHERE state_desc = 'DENY' GO USE foo -- permission in database GO SELECT class ,major_id ,grantee_principal_id ,permission_name ,state_desc FROM sys.database_permissions WHERE state_desc = 'DENY' GO
Here is the output.
So, as we can see, we have DENY permission on VIEW ANY DATABASE and that’s why we are seeing above behavior.
WORKAROUND / SOLUTION
We need to REVOKE the DENY permission which was given earlier.
USE master REVOKE VIEW ANY DATABASE FROM SQL1 USE foo REVOKE VIEW DEFINITION ON table_1 FROM SQL1
Here is the query which was used to create this scenario
USE master DENY VIEW any database TO SQL1 USE foo DENY VIEW DEFINITION on table_1 to SQL1
As a DBA, now you can avoid users to see the schema of database/table in SSMS. Have you used such lower level permissions in SQL Server?
Reference: Pinal Dave (https://blog.sqlauthority.com)