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.

SQL SERVER - Unable to See Tables (Objects) in SSMS hide-ssms-01

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.

SQL SERVER - Unable to See Tables (Objects) in SSMS hide-ssms-02

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.

SQL SERVER - Unable to See Tables (Objects) in SSMS hide-ssms-03

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)

, ,
Previous Post
SQL SERVER – Powershell Script – Remove Old SQL Database Backup Files from Azure Storage
Next Post
SQL SERVER – Management Studio Missing – SSMS 2014 not Installed with SQL Server 2014

Related Posts

1 Comment. Leave new

  • Carlos Guanes
    May 8, 2020 5:34 pm

    i wanted to use the Deny view definition to block user from seeing all objects list and definition but i found that when that option is set for a sql account and that account is being used for linked server any query with linked server will fail. how can i accomplish the same but allow linked server query?

    SELECT TOP 100 *
    FROM [SERVERSQL].[DATABASE].dbo.[TABLE]

    Msg 7314, Level 16, State 1, Line 1
    The OLE DB provider “SQLNCLI11” for linked server “SERVERSQL” does not contain the table “”[DATABASE]”.”dbo”.”[TABLE]””. The table either does not exist or the current user does not have permissions on that table.

    Thanks

    Reply

Leave a Reply

Menu