sp_helptext returns There is no text for object on a Stored Procedure

Question

sp_helptext returns There is no text for object on a Stored Procedure

Welsh Corgi

SSC Guru

Points: 116520
More actions
May 10, 2016 at 7:09 am

#307371

When I execute sp_helptext on a Stored Procedure it returns that there is no text for object on a Stored Procedure.
How can I grant read permissions to a Stored Procedure when they only have data reader prermissions?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 11 (of 11 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Welsh Corgi (5/10/2016)
When I execute sp_helptext on a Stored Procedure it returns that there is no text for object on a Stored Procedure.
How can I grant read permissions to a Stored Procedure when they only have data reader prermissions?

GRANT VIEW DEFINITION

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Michael L John One Orange Chip Points: 27054 More actions · Answer 2

1. Create a new database role, call it something like db_view_schema.

2. Add the users/groups into this role.

3. Grant VIEW DEFINITION to the desired schema's contained in the database to this role.

The users in this role will have the ability to see the schema of tables, functions, and so forth as well with this method.

You can alternately grant VIEW DEFINITION to each procedure individually, but that can become a bit cumbersome.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Sean Lange (5/10/2016)
Welsh Corgi (5/10/2016)
When I execute sp_helptext on a Stored Procedure it returns that there is no text for object on a Stored Procedure.
How can I grant read permissions to a Stored Procedure when they only have data reader prermissions?
GRANT VIEW DEFINITION

Thanks a lot!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Now I need to allow a group to be able to Script Table as ... on a Test Server.

It worked on Production for a Developer on Production by WITH THE GRAT VIEW DEFINTION but I can't get it to work on the Test Environment.

Any ideas?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Welsh Corgi (5/10/2016)
Now I need to allow a group to be able to Script Table as ... on a Test Server.
It worked on Production for a Developer on Production by WITH THE GRAT VIEW DEFINTION but I can't get it to work on the Test Environment.
Any ideas?

What do you mean by you can't get it to work? Exactly what are you trying to do and how are you doing it?

Can't see what you see from here.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I need to get the error message from the user.

Mail Server is down.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Welsh Corgi (5/10/2016)
I need to get the error message from the user.
Mail Server is down.
Thanks.

You do realize the more information you provide up front, the less back and forth there is getting all the relevant information.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Still not getting mail.

It is nearly 6:00 PM.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

sp_helptext works.

If you try generating a script table you get an error:

Index was outside the bounds of the array.

I need to check the version of SSMS that the user is using.

?

Recent

?

Sponsored

Answers

10 Random Facts To Impress People At Parties

? ? ? ?Archive ? Move ? ?Delete ?Spam ? ? More ? ? ? ?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

The problem was that the user was using SSMS 2008 against a 2012 Database.

Issue resolved.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Welsh Corgi (5/11/2016)
The problem was that the user was using SSMS 2008 against a 2012 Database.
Issue resolved.

Cool. Glad you solved it, and thanks for letting us know.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/