Hide schemas from users in MSSQL

Question

I'm creating a database with a few different schemas.

99% of users connect using Excel, and when they do they see all the system stuff:

enter image description here

This just confuses the users and I would like to configure SQL Server so that they only see UK_data and UK_reports.

So far I tried denying select on the things that I want to hide and only allowing select on the UK_ schemas, but it didn't work.

Is there a way to hide them from users?

Thanks!

• user212533: Does this answer your question? How To Hide Schema from user
• None: @bbaird Thanks for your suggestion but no. That approach let's me hide the data but not the schema. I want to hide the schemas and system things to avoid confusing the users..
• Francesco Mantovani: @Nacho so you just want to show a limited number of tables?
• None: @FrancescoMantovani something like that. I have a schema that has the tables with all the data. I created other schemas that only contain views to those tables. I want to limit which users can see which schemas, and therefore which views.
• Francesco Mantovani: That is even better @Nacho. So users can only have access to views. Through Excel?

Answer 1

I maybe found two solution for you:

1. HIDE

Hide Table "Person.Address"

EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

Show Table "Person.Address"

EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

2. DENY

`DENY VIEW DEFINITION ON Schema.Table TO UserName;`

Now UserName won’t be abe to see Table in Object Explorer. In Fact, they won’t be able to see the table in sys.tables or INFORMATION_SCHEMA.TABLES.

You can Use the DENY keyword to deny certain Users and REVOKE to Remove the existing permission.