CODEWITHSUNDEEP
sql-serverazure
Nimesh Gami
Nimesh Gami

Reputation: 381

I want to hide tables in SQL Server database

I've created a data sync process in Azure so that Azure has created few tables in my SQL Server database in the Datasync schema. I want to hide those tables that are located in the Datasync schema.

Can you guys please suggest how to avoid showing those tables in Azure, or how to hide tables from my SQL Server?

enter image description here

enter image description here

Upvotes: 4

Views: 16339

Answers (2)

Manish Dalal
Manish Dalal

Reputation: 1796

This question is somewhat old, but today I was looking for something similar so that I can organize tables in my database, either by hiding tables whose designing is completed, or placing them in some folder so that they do not interfere with the tables that I am currently working on (and confuse me :P ).

I found this article which discusses Hiding tables in SSMS Object Explorer

Using extended properties, the OP was able to hide tables from the view. You can read in more details in the article itself, but for this answer purpose, I am reproducing the OP's code here

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

I have just used this on one of my table dbo.Users.Logins, for that I had used

@level0name='dbo', and

@level1name='Users.Logins'

After executing the command, I refreshed the tables list, it took some 45 seconds to refresh (not sure it is usual or not) but after refresh the specified table's name was NOT in the list of tables

After removing the extended property, the table's name was back again.

Even when hidden, the table was working properly (SELECT, INSERT, UPDATE, DELETE and JOINS)

I am using SQL Server Management Studio v18.4 (© 2019)

HTH.

Upvotes: 7

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

There is No HIDE feature in SQL Server instead, you can Deny the permission to that Table for Certain Users of User Groups whom you do not want to view the Tables or Objects on your schema

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

Upvotes: 7

Related Questions