Solved

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

Posted on 2008-10-19
7
891 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

10 Experts available now in Live!

Get 1:1 Help Now