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.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS, T-SQL Tagged: microsoft sql server, SSMS, T-SQL