Restrict Metadata from SQL Endpoint and Semantic model.

[Bhargava05]

Hi,

I am working on restricting metadata visibility to users accessing the data through sql endpoint. i have tried using REVOKE, DENY View definition permissions and also other permissions, but they do not seem to hide the metadata form the database.

I also tried using DENY ALL ON OBJECT::dbo.dimension_employee TO [alice@mytenant.com]; However, this applies to all tables accross schemas except for explicitly granted ones, which is a desired outcome but isn't the right way i guess. My goal is to ensure that users only see tables and metadata that they have been explicity granted access to, without exposing other metadata.

Additionally, i would like to achieve same level of restriction in the semantic model, ensuing users can only view tables they have access to when querying via the sql endpoint.

Any suggestions are appreciated, Thanks!

[nilendraFabric · Accepted solution]

Hello @Bhargava05 

give it a try

Remove user from roles like `Default Readers` to prevent broad metadata access

REVOKE SELECT ON SCHEMA::dbo TO public;
DENY VIEW ANY DATABASE TO [alice@mytenant.com];

[v-tsaipranay · Accepted solution]

Hi @Bhargava05 
Thank you for posting in the Microsoft Fabric Community. Thank you @nilendraFabric  for your inputs.

To restrict metadata visibility in the SQL Endpoint while ensuring users only see explicitly granted tables, follow these steps:

Remove broad permissions from default roles (e.g., Default Readers) to prevent unnecessary metadata exposure. Also Deny metadata visibility database-wide using:

DENY VIEW ANY DATABASE TO [alice@mytenant.com];

Revoke schema-wide access for public:

REVOKE SELECT ON SCHEMA::dbo TO public;

Explicitly grant access only to the required tables:

GRANT SELECT ON dbo.dimension_employee TO [alice@mytenant.com];

For Semantic Model restrictions, apply Object-Level Security (OLS) in Power BI to control visibility at the table level.

By implementing these steps, you will only see tables they have explicit access to when querying via SQL Endpoint and in the Semantic Model.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[Bhargava05 · Accepted solution]

Hi @v-tsaipranay @nilendraFabric 

Thanks for your suggestions.

I did try dropping the user initially from datareader role and then also tried using DENY VIEW ANY DATABASE TO [alice@mytenant.com]; which seems to not work in fabric sql endpoint; it returned error "Permissions at the server scope can only be granted when the current database is master",   then i used DENY VIEW DEFINITION TO "user", i even used REVOKE SELECT ON SCHEMA::dboTO public;

None worked, even after executing all of them individually/together. User was still able to see metadata in the database.

When i use

DENY ALL ON OBJECT::dbo.table1 TO [user]; was able to hide metadata from the user for all the tables in all schema's, where it should have been applied to only table1.

[Bhargava05 · Accepted solution]

Hi @v-tsaipranay @nilendraFabric 

Thanks for your suggestions.

I did try dropping the user initially from datareader role and then also tried using DENY VIEW ANY DATABASE TO [alice@mytenant.com]; which seems to not work in fabric sql endpoint; it returned error "Permissions at the server scope can only be granted when the current database is master",   then i used DENY VIEW DEFINITION TO "user", i even used REVOKE SELECT ON SCHEMA::dboTO public;

None worked, even after executing all of them individually/together. User was still able to see metadata in the database.

When i use

DENY ALL ON OBJECT::dbo.table1 TO [user]; was able to hide metadata from the user for all the tables in all schema's, where it should have been applied to only table1.

[v-tsaipranay · Accepted solution]

Hi @Bhargava05 ,

Thanks for the update. Since Fabric SQL Endpoint doesn’t fully support DENY VIEW DEFINITION or REVOKE SELECT for metadata restriction, the best approach is:
Move restricted tables to a separate schema and only grant access where needed:

CREATE SCHEMA RestrictedSchema;
ALTER SCHEMA RestrictedSchema TRANSFER dbo.dimension_employee;
GRANT SELECT ON RestrictedSchema.dimension_employee TO [alice@mytenant.com];

This might prevent the table from appearing in metadata queries.

Use Object-Level Security (OLS) in the Power BI Semantic Model to fully restrict table visibility:

• Remove workspace-level model permissions. Apply OLS in Power BI Desktop → Model View → Security.

Check if metadata is still exposed:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo';

If restricted tables are still visible, schema separation might be necessary.

Since Fabric SQL Endpoint has limitations on metadata security, OLS is the recommended Microsoft approach.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay · Accepted solution]

Hi @Bhargava05 
Thank you for posting in the Microsoft Fabric Community. Thank you @nilendraFabric  for your inputs.

To restrict metadata visibility in the SQL Endpoint while ensuring users only see explicitly granted tables, follow these steps:

Remove broad permissions from default roles (e.g., Default Readers) to prevent unnecessary metadata exposure. Also Deny metadata visibility database-wide using:

DENY VIEW ANY DATABASE TO [alice@mytenant.com];

Revoke schema-wide access for public:

REVOKE SELECT ON SCHEMA::dbo TO public;

Explicitly grant access only to the required tables:

GRANT SELECT ON dbo.dimension_employee TO [alice@mytenant.com];

For Semantic Model restrictions, apply Object-Level Security (OLS) in Power BI to control visibility at the table level.

By implementing these steps, you will only see tables they have explicit access to when querying via SQL Endpoint and in the Semantic Model.

I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[v-tsaipranay]

Hi @Bhargava05 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[nilendraFabric · Accepted solution]

Hello @Bhargava05 

give it a try

Remove user from roles like `Default Readers` to prevent broad metadata access

REVOKE SELECT ON SCHEMA::dbo TO public;
DENY VIEW ANY DATABASE TO [alice@mytenant.com];