Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 903
  • Last Modified:

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

0
icongo
Asked:
icongo
  • 4
  • 3
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now