Solved

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

Posted on 2008-10-19
7
894 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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