I want users not to be able to see the objects under a schema , as well as the schema. I have denied their access to all these objects on select, update, etc, which is successful. Hence they can’t view the data or work with it! But they still can see the the name of the objects in the object explorer. I want to only see the objects they have access to, not the ones they don’t.
Many thanks for your help.
I am using Microsoft SQL Server.
It's not possible, as mentioned in this StackOverflow answer:
There is No
HIDEfeature in SQL Server instead, you can Deny the permission to that Table for Certain Users of User Groups...
The only thing you may be able to do is globally hide the objects for everyone, via extended properties, as discussed in the article Hiding tables in SSMS Object Explorer.
Example code for hiding the object:
EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO
Example code for unhiding it:
EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO
This is probably a terrible idea to do in a production server though, from a management perspective.
SQL Server automatically trims catalog queries (like select * from sys.tables) based on the user's permissions.
The ability to view the metadata on an object is controlled by the VIEW DEFINITION permissions. The VIEW DEFINITION permission is implied by any other permissions unless denied.
So this:
want to only see the objects they have access to, not the ones they don’t.
Is the behavior if you make sure to grant no access to the other objects. If they can see the objects, they have been granted VIEW DEFINITION on the object. Could be directly, or on the schema or the database, or the permissions might have been added to the PUBLIC role.
