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

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


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



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

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

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

    Sugeshkumar Rajendran
    SQL Server MVP

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


    Microsoft SQL Server MVP

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

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


    Microsoft SQL Server MVP

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


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




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

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

    Microsoft SQL Server MVP

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

  • grant VIEW DEFINITION on your_stored_procedure to dev_users

    will solve the problem.

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

    At a database level:

    USE [myDb]


    Or at the SQL instance level, for all dbs:

    USE [master]


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

  • 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

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

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

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