Hiding tables in SSMS Object Explorer

4

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

4 thoughts on “Hiding tables in SSMS Object Explorer

  1. Wu says:

    DBDefence can hide tables completely even from DBA. If DBA would query such secret table, he will get “table not found”

  2. Paul Schonier says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: