March 24, 2009 at 3:12 pm
Hello:
I have a table by name "notes" that contains a column "create_date" with a default value of getdate(). When I run this query below as "sysadmin" or "dbo", I get the "column_default" correctly populated as (getdate())
select * from information_schema.columns where table_name='notes'
However, when a non sysadmin user who has select, insert and update permissions on the table runs the same query, the "column_default" value shows as NULL.
Why is this happening?. It appears that only sysadmin can get the default column values in the catalog view?.
Basically, I need a way for a normal user (not just for sysadmin) to get the default value for all columns in a table.
Can someone help ?
Thanks,
Ganesh
March 24, 2009 at 3:28 pm
H Ganesh
This depends on definition of this view. Use this on master (maybe works also on other database) to see the view creation statement:
DECLARE @v nvarchar(max)
SELECT @v = definition from sys.all_sql_modules WHERE object_id = OBJECT_ID('INFORMATION_SCHEMA.COLUMNS')
PRINT @v
It uses the object_definition. Here is an extract from BOL about it:
Returns NULL on error or if a caller does not have permission to view the object.
A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_DEFINITION may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.
... same applies to sys.default_constraints.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 24, 2009 at 3:36 pm
But the user has permission for select, insert and update on the table?. Is that not enough to get the default values?
Anyway, I need to know a way for a normal user (not just for sysadmin) to get the default value for all columns in a table.
Is there a way to do so?
Thanks,
Ganesh
March 24, 2009 at 3:50 pm
Hi
The permissions are not needed for the INFORMATION_SCHEMA.COLUMNS but for the objects.
You have to GRANT the "VIEW DEFINITION" privilege for the tables to show the defaults for. (I hope this is available in SQL Server 2005 - at the moment I have only a SSE2K8)
GRANT VIEW DEFINITION ON YourTable TO test WITH GRANT OPTION
I tried and it works.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply