Information Schema Views in SQL Server 2005

Hi

I am having a minor problem with SQL Server 2005.

I have a requirement that certain users (developers) should have visibility of certain schema information using either Management Studio, or from an application...

The problem is that when we do a query on the information schema views (from accounts that have db_DataReader and db_DataWriter and Public role membership) the column_default value is not returned, (a null value is returned) even if there is definately a default value constraint.

What permisions do I have to give these individuals, dbo is NOT an option as they should only have restriceted access to the Database.

I look forward to your answers.

Kind regards

Ronnie

As for as this one is queried within the schemaview you should either have access to the function
object_definition or the syscolums table, which you should have if you see the column names. (Don′t think that there is a more granular permission set than on the table)

object_definition(c.default_object_id)) AS COLUMN_DEFAULT,

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

The minimum permission required to view the default information for a table column is "ALTER" permission on the table.

Of course, ALTER also means that the principals with that permission can ALTER the table schema or even drop the table. If you wish to restrict that and want the principals to only view the table schema including the definition of the default constraint grant them "VIEW DEFINITION" permission on the table.

Please refer to the topic "Metadata visibilty configuration" in books online which explains this in greater detail.

http://msdn2.microsoft.com/en-us/library/ms187113.aspx

Hope that helps

Thanks

Asvin

|||Interesting point, I was afraid of metadata security in SQL 2k5, but why the hell did you connect it to ALTER ? This thing is not explained in the BOL article.

-Jens.|||

It is not always connected to ALTER. It depends on what kind of metadata you are looking for. If it's just the name or id of the table or procedure then any permission on the object is sufficient. We use a higher privilege like ALTER only when you need access to "business logic". By business logic I mean things like the body of the stored procedure, definition of a computed column, the definition of a default or check constraint.

Take a look at http://msdn2.microsoft.com/en-us/library/ms191507.aspx.