column descriptions in sysproperties & its permissions

[tats]

I have 2 questions regarding column descriptions stored in sysproperties.

After many hours of searching (as it is not documented), I finally found where the column descriptions are stored and how to retrieve them (this newsgroup helped me to find some of the answers). But I'm puzzled, why column descriptions are not stored in syscolumns with other column-related info?

I was also wondering why sysproperties table does not have public permissions to SELECT (while most other system tables have public permission to select by default). It is as if such column info is protected. I can't see why it should be. Perhaps, I'm missing something. Does anybody know the answer to these questions?

The reason I'm asking is because I have a .NET web user control that uses column descriptions as control tooltips. However, it doesn't have administrators access rights, therefore it does not have permission to access sysproperties.

Thanks.

 

[tlbroadbent]

I'm sure that the properties such as description are stored in a sysproperties table because there can be multiple properties per object type including columns. These "extended" properties can include user defined properties.

I agree that Microsoft poorly documented the extended properties available in SQL 2000. However, you can find considerable info if you seqrch SQL BOL for extended properties. Access to the properties is granted to users through the fn_listextendedproperty function. Permission to list extended properties with this function varies with type of property and user permissions on parent objects. I'm certain that you could use fn_listextendedproperty from the .NET appication to retrieve column descriptions. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tats]

Thanks, Terry, for your prompt reply. You've confirmed that using fn_listextendedproperty function is the way to go. It works perfectly in the SQL query analyser.

Unfortunately, for some reason, this function does not return the 'value' field (which is the description) when run from an ASP.NET page. I'm not sure whether it is to do with the ASP.NET page or SQL server or the permissions of the objects I'm trying to access.

Here's the statement I'm using for Presentation table:
select * from ::FN_LISTEXTENDEDPROPERTY('MS_Description', 'User','dbo','table','Presentation', 'Column', default)

From the web page, this returns objtype, objname and name, but that's all.

Is there anything glaringly wrong with this?

Thanks again.

 

[tats]

I've worked it out!

Although the datagrid control, to which I bind the result of my query, does not display the 'value' column, I can still extract the descriptions programmatically.

Case closed.