SQL Server 2005, non sysadmin get default value for a column

I have a user (non sysadmin) that needs to query the default value of a column. This was previously possible in sql 2000. Using the query attached.

In 2005 running the same query returns a value of NULL. Is it possible for a non-sysadmin to run a query to determine the default value and if possible the query compatible with 2000 and 2005?
SELECT table_name, column_name, LEFT(is_nullable,1)
,UPPER(LEFT(data_type,12))
,CAST(ISNULL((CAST(numeric_precision AS VARCHAR))
, character_maximum_length) AS VARCHAR) as max_length 
, RTRIM(column_default) 
FROM information_schema.columns WHERE table_name = ?

Open in new window

icongoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
A non-admin user can run the query, but permission to view the system tables is required.  Make sure the users are assigned to the db_datareader role.  

Alternatively, you can save the query in an sproc with the EXECTUTE AS clause to switch security context to one that does have permission.

0
icongoAuthor Commented:
Can you give an example of how to do it in an SP, the query is actually called from an SP, but still doesn't work.
The user is part of the db_datareader role
0
dqmqCommented:
>The user is part of the db_datareader role

Then it should work.  Unless the permission of that role have been changed.  BTW, you can tell if the necessary permissions are granted by logging into Management Studio as the user of interest and drilling down to the system tables in object explorer.  If the system tables are visible, then the results of the InformationSchema views should also be visible.


>Can you give an example of how to do it in an SP

Assuming the owner of the SP has the necessary permissions:

Create Procedure YourSP
WITH EXECUTE AS 'Owner'
AS
....


0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

icongoAuthor Commented:
That worked however, when I run the sp, I get the following:
Msg 916, Level 14, State 1, Procedure update_sql_tables, Line 0
The server principal "sa" is not able to access the database "mydb" under the current security context.

Are there steps to resolve this?
0
dqmqCommented:
The way I interpret it is that SA owns the procedure, but does not have permission to access the database referenced in the procedure.   You can grant SA the necessary permissions or, instead of EXECUTE AS OWNER, you can do EXECUTE AS 'userid'  where 'userid' is any user that already has the necessary permissions.

0
icongoAuthor Commented:
The solution was the following:
grant view definition to {user_defined_role}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
Yes, I object.  I not only answered the question, but redirected the thread toward the underlying permissions problem (which wasn't even mentioned in the question).  I provided several ways to solve the problem and a variation of one was actually used.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.