Solved

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

Posted on 2008-10-19
7
892 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:icongo
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 22753244
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
 

Author Comment

by:icongo
ID: 22753363
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
 
LVL 42

Expert Comment

by:dqmq
ID: 22753699
>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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:icongo
ID: 22754374
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
 
LVL 42

Expert Comment

by:dqmq
ID: 22754738
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
 

Accepted Solution

by:
icongo earned 0 total points
ID: 22809099
The solution was the following:
grant view definition to {user_defined_role}
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22810279
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now