ADO Connection.OpenSchema works differently with SQL Server 2005

My application calls OpenSchema(adSchemaColumns, ...) on an ADO Connection
object. Using Profiler I can see that under the hood of SQL Server 2005
it's running "exec [dbname]..sp_columns_rowset N'tablename',N'dbo',NULL".
When I'm logged in as "sa" it correctly returns column default info
(COLUMN_HASDEFAULT, COLUMN_DEFAULT). But if I'm logged in as a regular
user, it doesn't return values for those columns. The regular user is
running under an approle that is a member of db_datareader and
db_datawriter.
I'm thinking this is a rights issue since it works for "sa". What
particular rights must I have in order to view column default data?
Thanks,
Troy"Troy Wolbrink" <wolbrink@.ccci.org> wrote in message
news:%23sivQF77FHA.444@.TK2MSFTNGP11.phx.gbl...
> My application calls OpenSchema(adSchemaColumns, ...) on an ADO Connection
> object. Using Profiler I can see that under the hood of SQL Server 2005
> it's running "exec [dbname]..sp_columns_rowset N'tablename',N'dbo',NULL".
> When I'm logged in as "sa" it correctly returns column default info
> (COLUMN_HASDEFAULT, COLUMN_DEFAULT). But if I'm logged in as a regular
> user, it doesn't return values for those columns. The regular user is
> running under an approle that is a member of db_datareader and
> db_datawriter.
> I'm thinking this is a rights issue since it works for "sa". What
> particular rights must I have in order to view column default data?
>
In SQL Server 2005 db_datareader no longer entails full metadata access.
For full access to object metadata you need to the "view definition"
privilege. You can specify this on the object, schema or database level.
To give access to the full metadata for every object in the database run:
grant view definition to [MyRegularUser]
or
grant view definition to [MyDomain\MyRegularUser]
David|||> In SQL Server 2005 db_datareader no longer entails full metadata access.
...
> To give access to the full metadata for every object in the database run:
> grant view definition to [MyRegularUser]
Thanks! That was exactly what I needed to fix this. Now my app is SQL
Server 2005 compatible!
--Troy