Post reply

How to allow developers to ''see'' proc text in a protected db environment

  • January 9, 2007 at 4:50 pm

    #176424

    I have a Test environment that we do not allow developers into.  They can read data, but this gets them into tables only.  I would like to allow them to view proc text also.  Not execute them.  But see the contents.

    I regularly use sp_helptext for this.  However, I have not been successfull in granting them access to this proc, and them being able to actually see procs.  Errors occur saying that the proc doesnt exist.  The proc they are wanting to look into...

    So, i must be doing something wrong...  I dont want to grant them ddl_admin rights, because that is too much. 

    Help me learn.  Thanks.

     

  • January 9, 2007 at 5:14 pm

    #682254

    Is the stored procedure encrypted?  sp_helptext won't display the text if it is.

    Greg

    Greg

  • January 9, 2007 at 5:32 pm

    #682258

    no, it is not encrypted.  And i am not granting to any particular proc.  I would like the deveoplers to have the ability to see any proc contents, without having to ask me for it.  They can have read access to the database.  But they cannot be allowed to alter anything.  So read on the proc contents should be permissible. 

  • January 9, 2007 at 9:48 pm

    #682287

    Are you sure they are running this from within the right database?

    Can you check the code in the master database to see if the system proc shows only the text of the projects you own / have access to?

  • January 9, 2007 at 11:04 pm

    #682299

    grant execute privilege on sp_helptext. i hope this should work. please give us what the error that the user is getting

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • January 9, 2007 at 11:15 pm

    #682303

    I don't think it is going to work if grant exec permission to sp_helptext because in SQL 2005 security is locked down completely...

    Just guess: One option might be create a procedure with EXECUTE AS option and grant the permission to the created procedure to developers..

     

    MohammedU
    Microsoft SQL Server MVP

  • January 10, 2007 at 10:00 am

    #682451

    here is the exact error that i get when i execute sp_helptext as a db_datareader with execute rights on sp_helptext

    Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

    The object 'USP_IE_GET_INVOICES_BY_USER_ID' does not exist in database 'ASE_WWF' or is invalid for this operation.

    The stored proc that i am looking for does exist.  I, as an admin, can see it exists.

    Obviously i could not access sp_helptext with this user, prior to being granted execute rights on it.

     

    I think that Mohammed Uddin had it right with the 2005 security being locked down.  However, with each lockdown, there should be a way to allow it.  Its only locked down by default.  I would imagine that they (MS) would have provided us a way to hang ourselves, if that was what was wanted.  They allow us to enable xp_cmdshell... though that is disabled as default.

    I just dont know what is needed to allow this security...

  • January 10, 2007 at 11:45 am

    #682507

    Last night I tried diffent ways without success...let us see if any one comeup anthing...

     

    MohammedU
    Microsoft SQL Server MVP

  • January 10, 2007 at 1:13 pm

    #682543

    as a developer will this allow them to see the text?

    SELECT TEXT FROM SYSCOMMENTS WHERE ID=object_id('USP_IE_GET_INVOICES_BY_USER_ID')

    if that does work, you could create your own proc and grant them access, ie :

    CREATE PROCEDURE PR_HELPTEXT( @obj sysname)

    AS SELECT TEXT FROM SYSCOMMENTS WHERE ID=object_id(@obj)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 10, 2007 at 5:48 pm

    #682585

    Only option I have seen is if you GRANT ALTER permission to the user he can see the text but can't execute...

    ALTER permission is dangerous

    Lowell method will not work...

    MohammedU
    Microsoft SQL Server MVP

  • January 10, 2007 at 7:35 pm

    #682594

    Just thaught of a work around.  This is untested because I don't have 2005 installed at home.

    Make a reporting table with whatever data the developpers need to see.  Insert the data in the reporting table once.  Grant them access to that table.  Then create a DDL trigger that updates that table whenever necessary.

  • October 12, 2007 at 10:17 am

    #741670

    grant VIEW DEFINITION on your_stored_procedure to dev_users

    will solve the problem.

  • October 12, 2007 at 1:22 pm

    #741719

    This technique will work nicely. See the topics in BOL for VIEW DEFINITION for more explanation.

    At a database level:

    USE [myDb]

    GRANT VIEW DEFINITION TO myUser

    Or at the SQL instance level, for all dbs:

    USE [master]

    GRANT VIEW ANY DEFINITION TO myLogin

    You can also grant this permission at the object level (i.e., a specific stored procedure)...see BOL.

  • November 29, 2007 at 2:53 pm

    #755707

    Thanks for such valuable information! I have developers that

    need this access and I have been searching high and low for the info.

    You the same stuff everywhere about GRANT VIEW DEFINITION. However,

    a lot of the stuff is convoluted with a bunch of mumbo jumbo without the

    meat and potatoes.

    Thanks Again

  • April 10, 2008 at 3:51 pm

    #801218

    Should simply be able to grant view definition - just tried it with a developer and it works.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply