Hide All database objects in SQL endpoint for users with read only access

[Bhargava05]

When a lakehouse is shared with an user without any additional permissions. He is able to connect to the lakehouse through SQL end point and see all the schema, table names and column names. But since he only has read access, he won't be able to query the data.

What I'm trying to do is, if a user has only read access, he shoudn't see any of the database objects like schema names, tables names etc. Everything has to be granted explicitly for the user to have access or to even see the schema name, table name through the sql end point.  I know using grant, i can let users query the data. But just with read only access as well, they are able to see all of the database objects which i wanted to restrict them from unless granted access. How do i do that. Any suggestions are much appreciated

[v-sgandrathi · Accepted solution]

Hi @Bhargava05,

Thank you for using Microsoft Community Forum.

To prevent users from seeing any database objects unless explicitly granted, you need to revoke metadata visibility using the VIEW DEFINITION permission. This ensures users cannot even see the existence of schemas, tables, or columns.
You can refer to the following document for more information:

REVOKE (Transact-SQL) - SQL Server | Microsoft Learn

To further restrict access, you can explicitly deny direct access to specific objects if users were previously granted permission. This ensures they cannot view or query particular tables, even if other permissions are in place.
I’m sharing the DENY SQL link for your idea:

DENY (Transact-SQL) - SQL Server | Microsoft Learn

If you want the user to access specific tables, you need to explicitly grant them permission.
By following these steps, you can ensure that users with read-only access cannot see any database objects unless explicitly granted permission.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Regards,
Sahasra.

[Bhargava05 · Accepted solution]

Hi @v-sgandrathi thanks for your reply!

I did try already revoking/denying view definition permission to the user. And i did try it back right now.

These were the intial permissions, we had on the sql endpoint before revoking or denying. 

I used REVOKE VIEW DEFINITION ON DATABASE::lakehouse_tst FROM"user" first and then also

DENY VIEW DEFINITION ON DATABASE::lakehouse_tst TO "user", which resulted in 

But still the user was able to see the metadata in the database.

And also is there a way where i can revoke view definition to all of the users instead of specifying each at a time? Like revoking view definition to public? I did try that as well, but didnt work tho.

Let me know your thoughts!

Would be of great help!!

Thanks!

[v-sgandrathi · Accepted solution]

Hi @Bhargava05,

The reason the user still sees metadata, even after using REVOKE and DENY VIEW DEFINITION, is likely due to inherited permissions, especially from the public role, which all users are part of by default. Even if you revoke permissions from a specific user, they may still inherit visibility from this role or other implicit grants. To address this, ensure no unwanted permissions remain on the public role or any other roles the user belongs to. For more control, consider creating a custom restricted role with no default visibility, granting access only where needed. If visibility persists, this may be due to how Microsoft Fabric’s SQL endpoint handles permissions, and workspace-level permissions may provide a more effective alternative for restricting access.

Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.

Thank you.

[Bhargava05 · Accepted solution]

@v-sgandrathi  There are no additional permissions to public role or even for the user. So even if i create a custom role, They may still inherit whatever from public role has and still be able to see the metadata right. I even tried revoking and denying view definition to public, which still didnt restrict the user from viewing all the metadata

[Bhargava05 · Accepted solution]

@v-sgandrathi  There are no additional permissions to public role or even for the user. So even if i create a custom role, They may still inherit whatever from public role has and still be able to see the metadata right. I even tried revoking and denying view definition to public, which still didnt restrict the user from viewing all the metadata

[v-sgandrathi · Accepted solution]

Hi @Bhargava05 ,

Since you have already applied Revoke and Deny view definition to both the specific user and the public role, but the metadata remains visible, this behavior is likely due to the underlying permission model in Microsoft Fabric’s SQL endpoint. Unlike traditional SQL Server, some services—including Fabric—may handle Deny view definition differently or override it in certain scenarios.

As the feature is still in public preview, there may be limitations affecting permission enforcement. We recommend monitoring updates for the general availability release, as this behavior may be addressed in future versions.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[Bhargava05 · Accepted solution]

Hi @v-sgandrathi thanks for your reply!

I did try already revoking/denying view definition permission to the user. And i did try it back right now.

These were the intial permissions, we had on the sql endpoint before revoking or denying. 

I used REVOKE VIEW DEFINITION ON DATABASE::lakehouse_tst FROM"user" first and then also

DENY VIEW DEFINITION ON DATABASE::lakehouse_tst TO "user", which resulted in 

But still the user was able to see the metadata in the database.

And also is there a way where i can revoke view definition to all of the users instead of specifying each at a time? Like revoking view definition to public? I did try that as well, but didnt work tho.

Let me know your thoughts!

Would be of great help!!

Thanks!

[v-sgandrathi · Accepted solution]

Hi @Bhargava05,

The reason the user still sees metadata, even after using REVOKE and DENY VIEW DEFINITION, is likely due to inherited permissions, especially from the public role, which all users are part of by default. Even if you revoke permissions from a specific user, they may still inherit visibility from this role or other implicit grants. To address this, ensure no unwanted permissions remain on the public role or any other roles the user belongs to. For more control, consider creating a custom restricted role with no default visibility, granting access only where needed. If visibility persists, this may be due to how Microsoft Fabric’s SQL endpoint handles permissions, and workspace-level permissions may provide a more effective alternative for restricting access.

Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.

Thank you.

[v-sgandrathi]

Hi @Bhargava05,

I wanted to check in your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply as Accepted solution and give Kudos that helped you. It would be greatly appreciated by others in the community who may have the same question.

Thankyou for using Microsoft Fabric Community Forum!

[v-sgandrathi · Accepted solution]

Hi @Bhargava05,

Thank you for using Microsoft Community Forum.

To prevent users from seeing any database objects unless explicitly granted, you need to revoke metadata visibility using the VIEW DEFINITION permission. This ensures users cannot even see the existence of schemas, tables, or columns.
You can refer to the following document for more information:

REVOKE (Transact-SQL) - SQL Server | Microsoft Learn

To further restrict access, you can explicitly deny direct access to specific objects if users were previously granted permission. This ensures they cannot view or query particular tables, even if other permissions are in place.
I’m sharing the DENY SQL link for your idea:

DENY (Transact-SQL) - SQL Server | Microsoft Learn

If you want the user to access specific tables, you need to explicitly grant them permission.
By following these steps, you can ensure that users with read-only access cannot see any database objects unless explicitly granted permission.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Regards,
Sahasra.