Hiding tables in SSMS Object Explorer
13April 3, 2017 by Kenneth Fisher
Extended Properties
Sometimes you find out the strangest things about SQL Server. I’ve written about extended properties before but had no idea they actually did anything beyond documentation. Then the other day I was reading a forum entry (no clue why) and saw a really interesting answer to the question. It was a few years old so I had to test it for myself.
Oh. You probably want to know what the answer was, right? You can actually hide an object from object explorer by assigning a specific extended property.
And assign the property:
EXEC sp_addextendedproperty @name = N'microsoft_database_tools_support', @value = 'Hide', @level0type = N'Schema', @level0name = 'Person', @level1type = N'Table', @level1name = 'Address'; GO
Then refresh the list.
You can then remove the propert (and the object will show back up) like so:
EXEC sp_dropextendedproperty @name = N'microsoft_database_tools_support', @level0type = N'Schema', @level0name = 'Person', @level1type = N'Table', @level1name = 'Address'; GO
FYI I’ve tried this at the column and schema levels and it didn’t work.
Using this you can hide the object from SSMS object explorer without restricting its use in any way.
I’m curious if there are any other hidden uses of extended properties. I haven’t been able to find any documentation so if you’ve seen any please let me know!
DENY VIEW DEFINITION
This one is pretty easy.
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.
VIEW DEFINITION is the ability to see the definition of the object (duh). In the case of SPs the code, same with Views and in the case of Tables it’s the columns definitions etc.
Filtering
I’m not going to talk about using filters here for a couple of reasons. Firstly I’ve already talked about it but also because, unlike the other two options, it only affects the specific SSMS install where you create the filter.
Compare and Contrast
VIEW DEFINITION is more flexible. You can restrict an entire schema or a single table. You can also restrict the view to a single user, role, or everyone. It also affects the system views which is nice.
On the other hand, it does require a DENY which I find can cause confusions. I’ve had several occasions where permissions didn’t work as expected and when people would come to me for help it would turn out to be a long forgotten DENY. Not the fault of DENYs themselves but it does trip people up occasionally.
Now Extended Properties on the other hand, while it’s far more restrictive (Tables only and affects everyone) it doesn’t require the DENY. Also interestingly it affects sysadmins while the DENY doesn’t.
On the whole, I think it’s a good idea to know both methods and make a decision based on your exact requirements.
SQL Studies 
DBDefence can hide tables completely even from DBA. If DBA would query such secret table, he will get “table not found”
Hmm, Sounds interesting. I wonder how they block a sysadmin from seeing a table in the metadata.
If Sys admin doesnt have access, how to unhide the table and who can do it?
It’s not truly hidden, it’s just like a filter in SSMS I believe.
This is a very interesting article, and is something I have recently stumbled across in our 2012 environment. I don’t see the administrative purpose of the extended property functionality given that it hides the object from the sa as well. In my case the hidden tables are used by a production application, and what ever former DBA hid them is long gone. They were discovered when another employee imported all tables from the database into Access.
I can see places where you would want to use it. For example an environment where you have more SAs than you really should and tables you want to “hide”. Note that it only hides them from the GUI and not any code. You can also see them in sys.tables etc. I’ll agree though it’s of pretty limited use.
On the other hand extended properties in general are really quite useful. For example you can put on one users/logins to add notes about who they were created for and why.
Two problems with this solution. Firstly, the Value can be anything you like, including an empty string. ‘Hide’ is not a problem, but neither is it significant. Just ends up as the name of the property. Secondly,and more significantly, it doesn’t hide the table in the GUI, just moves it to the System Tables node, as what you are ding is telling SQL it is a ‘microsoft_database_tools_support’ type table, e.g. as used for database diagrams. It is still perfectly visible in the GUI, just in a slightly different place.
Cool! I wasn’t aware that it was just moved. I appreciate the additional information. I still find it interesting but you are probably right. Not as useful as I’d hoped.
Hi. Thanks for your great post. I want to know if we can hide an object from the object explorer by using designer. Without the sql queries.
Go into the properties of the table. The very bottom tab is extended properties. Just add the description/value there.
Its good but I cannot hide a trigger : Error
Cannot find the object , because it does not exist or you do not have permission
This is work for me
EXEC sp_addextendedproperty
@name = N’microsoft_database_tools_support’,
@value = ‘Hide’,
@level0type = N’Schema’, @level0name = ‘dbo’,
@level1type = N’Table’, @level1name = ‘Person.Address’;
EXEC sp_dropextendedproperty
@name = N’microsoft_database_tools_support’,
@level0type = N’Schema’, @level0name = ‘dbo’,
@level1type = N’Table’, @level1name = ‘Person.Address’;
Very cool!