No compatibility view for sysproperties

Is there a reason that there isn't a compatiblity view for sysproperties.Try searching for sysproperties in SQL Server 2000 Books Online and you'll see why. There is a

reason why we don't recommend to use undocumented features... :-)

--

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

Blog: http://solidqualitylearning.com/blogs/tibor/

wrote in message

news:de2325cd-c357-4bf7-8f97-79553d33a82b@.discussions.microsoft.com...

> Is there a reason that there isn't a compatiblity view for

> sysproperties.

>|||

Yes I know,

However the policy for 2005 seems to have been to lay the cards on the table and finally state that it won't be supported in next release.

Its a shame because the system function is just a bit clunky. Nice to see sys.extendedproperties (of something like that).

|||

Fair enough, so what is the official supported way of reading extended properties defined on the database WITHOUT needing excessive permissions like db_owner or db_ddladmin?

I used select name, value from sysproperties where id=0 under SQL2K as these were not returned by fn_listextendedproperty when running as an application user, but I needed to read custom information from each database available to the user.

It would seem that sys.extended_properties does not show these either, so I am going to have great fun getting our application to a state where it can run under both SQL2K and SQL2K5!

|||

The official way of reading extended properties in sql server 2005 is to use the catalog view sys.extended_properties or the system function sys.fn_listextendedproperty.

In SQL Server 2000, we only support the system function fn_listextendedproperty

|||Ok, so I take it that the answer is that there is no way to do what I am trying to do ie read db level extended properties without db_owner or db_ddladmin.|||

By db level extended properties do you mean extended properties on the database itself or extended properties on database objects like tables, procedures , etc.

If it is the latter you only need to have some permission (select, insert, references, etc) on the object to view its extended properties. if it is the former you can grant the VIEW DEFINITION permission to the user to view the extended properties on the database. Either case, you do not need to be db_owner or db_ddladmin

|||

Thanks Asvin,

I am talking about extended properties defined at the database level ie id=0 under sql2k and class=0 under sql2k5.

VIEW DEFINITION works a treat under 2005 but I am struggling to find an equivalent under 2000. Unfortunately at this stage I have to find a solution that works for both.

|||OK. If the solution needs to work on sql2k then you are right; the user needs to be member of db_ddladmin or db_owner to view extended properties on the database. Unfortunately, the permission model in sql2k is not fine grained enough.