SQL SERVER – Unable to See Tables (Objects) in SSMS

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 (http://blog.SQLAuthority.com)