From: Iter on
Hi Guys
I have a id that has dbreader role. When I use this ID to run the query
select * from sys.information_schema.columns, the column column_default
returned null although I have some default value in the tables. It returned
value when I use id that has dbowner permission to run it. Can anyone let me
know what minimum permsion can I give to the id so that column_default can
return correct value when I run the query. Thanks.
From: Tom Cooper on
grant view definition on the table.

Tom

"Iter" <Iter(a)discussions.microsoft.com> wrote in message
news:60185772-0E13-4FE1-8FA0-622926547775(a)microsoft.com...
> Hi Guys
> I have a id that has dbreader role. When I use this ID to run the query
> select * from sys.information_schema.columns, the column column_default
> returned null although I have some default value in the tables. It
> returned
> value when I use id that has dbowner permission to run it. Can anyone let
> me
> know what minimum permsion can I give to the id so that column_default can
> return correct value when I run the query. Thanks.

From: Erland Sommarskog on
Iter (Iter(a)discussions.microsoft.com) writes:
> I have a id that has dbreader role. When I use this ID to run the query
> select * from sys.information_schema.columns, the column column_default
> returned null although I have some default value in the tables. It
> returned value when I use id that has dbowner permission to run it. Can
> anyone let me know what minimum permsion can I give to the id so that
> column_default can return correct value when I run the query. Thanks.

GRANT VIEW DEFINITION ON SCHEMA::dbo TO someuser

Normally, you have the permission to view metadata for a table, if you have
a SELECT permission. This does not extend to defaults, which certainly isn't
intuitive, but I think this is due to that defaults are objects on their
own.

I have submitted a bug:
https://connect.microsoft.com/SQLServer/feedback/details/582973/user-with-select-permission-on-a-table-cannot-see-definition-of-default
But to be honest, I expect it to be closed by design.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx