Have you ever encountered a situation where a user can see the data from a table but not the table itself in SQL Server? This can be a puzzling scenario, but it can be easily explained by understanding the concept of permissions in SQL Server.

Recently, I came across a similar situation when a client reported that they were unable to see a table in SQL Server Management Studio (SSMS), even though they could query the table and retrieve data from it. To investigate further, I asked the client to provide more details and they shared screenshots showing the discrepancy.

In the screenshots, it was evident that a sysadmin level account was able to see both the database and the table in SSMS. However, a non-sysadmin user, who was not able to see the database in the object explorer, could still query the table successfully.

To identify the root cause of this issue, I decided to capture a SQL Server Profiler trace while opening SSMS. It didn’t take long to discover that there was a DENY permission applied somewhere. To confirm this, I asked the client to execute the following queries:

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 -- replace 'foo' with the actual database name
GO
SELECT class, major_id, grantee_principal_id, permission_name, state_desc
FROM sys.database_permissions
WHERE state_desc = 'DENY'
GO

The output of these queries revealed that there was a DENY permission on VIEW ANY DATABASE, which explained the behavior observed by the client.

To resolve this issue, we needed to revoke the DENY permission that was previously granted. The following queries were executed:

USE master
REVOKE VIEW ANY DATABASE FROM SQL1 -- replace 'SQL1' with the appropriate user or role
USE foo -- replace 'foo' with the actual database name
REVOKE VIEW DEFINITION ON table_1 FROM SQL1 -- replace 'table_1' with the appropriate table name and 'SQL1' with the appropriate user or role

It’s important to note that the scenario was created using the following queries:

USE master
DENY VIEW ANY DATABASE TO SQL1 -- replace 'SQL1' with the appropriate user or role
USE foo -- replace 'foo' with the actual database name
DENY VIEW DEFINITION ON table_1 TO SQL1 -- replace 'table_1' with the appropriate table name and 'SQL1' with the appropriate user or role

As a database administrator (DBA), it’s crucial to understand and manage permissions effectively in SQL Server. By utilizing lower-level permissions like DENY, you can control the visibility of schemas, databases, and tables in SSMS.

Have you encountered similar situations where permissions affected the visibility of database objects in SQL Server? Share your experiences and insights in the comments below!