2

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!

6
  • Does this answer your question? How To Hide Schema from user
    – user212533
    Mar 8, 2021 at 14:33
  • @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..
    – None
    Mar 8, 2021 at 14:45
  • @Nacho so you just want to show a limited number of tables? Mar 8, 2021 at 20:45
  • @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.
    – None
    Mar 9, 2021 at 8:56
  • That is even better @Nacho. So users can only have access to views. Through Excel? Mar 9, 2021 at 13:11

1 Answer 1

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
  1. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.