Getting text of system stored procedures in 2005

Question

Getting text of system stored procedures in 2005

Yelena Varshal

SSC-Dedicated

Points: 34286
More actions
January 4, 2005 at 3:31 pm

#65870

Hi,
I wanted to get a text of a stored procedure in SQL Server 2005 and I could not find it. Nowhere to right-click and select properties. I also could not use sp_helptext. It would say
Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 95
There is no text for object 'sp_adduser'.
I am perfectly able to run sp_helptext 'sp_adduser' in SQL Server 2000. This is important because I have to modify system procedures for some applications. I put the same topic under Administration section too.
Yelena
Regards,Yelena Varsha

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

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

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

BOL for SQL Server 2000 says: "This procedure is included for backward compatibility. Use sp_grantdbaccess." So maybe they finally took it out in Yukon?

There is no "i" in team, but idiot has two.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Dave,

This procedure was used as an example. I can not see text of ANY stored procedure

Regards,Yelena Varsha

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 3

It appears that in SQL 2005, not all internal sprocs are editable. Look in the master.sys.syscomments table and you'll see code for only some of the sprocs.

Most likely Microsoft has done this for both security and performance reasons.

Remember that system procedures are for internal use of SQL Server. I would recommend creating a wrapper sproc with additional functionality where needed.

Julian Kuiters
juliankuiters.id.au

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 4

This is correct. Microsoft is working hard on this version to protect the schema more. Therefore there are a lot of things they're putting in, like system views, to try and prevent a lot of the information disclosure you get in the previous versions.

K. Brian Kelley
@kbriankelley

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Brian,

Thanks!!!.

Now we have to work on the exit strategy. I do have one system procedure modified to comply with the business process. I will have to work with the application vendor to modify the implementation of the feature based on what you said.

I suggest we somehow compose a list of the critical changes in 2005 that will affect the development. I mean a list of things that is not really in the "WHAT IS NEW IN SQL Server 2005" official list.

Yelena

Regards,Yelena Varsha

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 6

After doing some checking, it is possible. However, not so with sp_helptext as in SQL Server 2000. Now you must use object_definition().

SELECT OBJECT_DEFINITION(<object ID>)

As an example:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_help'))

K. Brian Kelley
@kbriankelley

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Brian,

Thanks. I tested and it worked for me. I was able to get the text of system SPs. All- do not forget to change the query options under Results - Text to display more then 256 characters. It is curious: it is 256 default as text (I new it) but it is 65535 default for the grid.

I was able to save the text to another stored procedure that I created. New experience: when I ran

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_adduser'))

the returned text started as:

create procedure sys.sp_adduser

but when I modified the name to sys.z_old_adduser it would not get it telling me that the schema sys does not exist or permission denied. It is understandable, I was not able to create or modify a system stored procedure in the previous versions without losing the definition "system" too.

I had no problems saving the test of the procedure as a dbo schema (former usage said Owner, not schema, it is OK)

I will now try to actually modify the system thing using Alter Procedure and see if it lets me. (All- Don't do it on your production servers, I am doing it on the system where I can re-install whenever I want - YV)

Yelena

Regards,Yelena Varsha

SonomaRik Old Hand Points: 367 More actions · Answer 8

we are getting the same results, but when I exec it as dbo I CAN see the results.

The problem exists for a user we created with datareader, and MSDynamicsSL. We get the "15197: There is no text for object . . . "

So, I don't believe it is a MS 2005 problem unless addressing the specific items being hidden above.

The wonder here is: why the hidden text on local objects for

following query:

use slscisdapp

exec sp_helptext 'dbo.xp_sc_ssrs_projectprofile'

"

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 9

rgranucc (3/2/2009)
we are getting the same results, but when I exec it as dbo I CAN see the results.
The problem exists for a user we created with datareader, and MSDynamicsSL. We get the "15197: There is no text for object . . . "
So, I don't believe it is a MS 2005 problem unless addressing the specific items being hidden above.
The wonder here is: why the hidden text on local objects for
following query:
use slscisdapp
exec sp_helptext 'dbo.xp_sc_ssrs_projectprofile'
"

xp_ is usually the prefix for an extended stored procedure. If that's the case, there isn't any T-SQL code. It's in a .DLL. If you look in Books Online for extended stored procedure, that should make it a bit clearer.

K. Brian Kelley
@kbriankelley

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 10

Hi

Another way:

DECLARE @v-2 NVARCHAR(MAX)

SELECT @v-2 = definition

FROM master.sys.all_sql_modules

WHERE object_id = OBJECT_ID('sp_adduser')

PRINT @v-2

Attention

The PRINT function is restricted to 8000 characters for VARCHAR and 4000 characters for NVARCHAR! If you really want to see all the SQL use a client application or split the result.

Greets

Flo

SonomaRik Old Hand Points: 367 More actions · Answer 11

ok, perhaps I'll have to dig deeper to understand how to set up a typical user to get a simple sp_helptext

nice to have a simple check box system setup

BTW, as a followup, I DID get that user their rights to exec

seems, never know why MS has always allowed multiple entrance points to check an item rather than just one screen, that when I displayed under permissions for the particular object, one user had more than the other. I gave ALTER and CREATE VIEW under the PERMISSIONS of that Database, and viole: SOLVED.

giving the rights to the db under the USER SECURITY for MS SQL 2005 did NOT accomplish this....much more 'things to check'

Surendra Patle SSC-Addicted Points: 407 More actions · Answer 12

Resolved the issue of SP_Helptext which was not showing Storeed procesdure code earlier and throwing error like :- "There is no text for object" and all USP which MrBatch databse were showing as locked.

Resolution:-

The definition of user objects is only visible to the object owner or grantees which have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION.

So provided 'ALTER' permission to 'Galactica' user on this DB and all went fine then..

Please try this..

Thanks

SurendraP....