|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sp_helptext: "There is no text for object "I create a new SQL Server login in a SQL Server 2005 database and add him to
db_datareader and db_datawriter roles. I also grant him execute to a stored procedure. I log on as this new login. I can execute the store proc but when I try to view the code I get the error: sp_helptext myProc Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107 There is no text for object 'myProc'. Can anyone explain why? who is the schema of myproc? Is it dbo or something else?
If you create stored procedures this way: CREATE PROCEDURE myproc AS .... You're probably going to want to stop doing that in SQL Server 2005, especially if you do so with users that are created with a default schema other than dbo. But even in 2000, it is usually considered a best practice to prefix your objects with the owner name ... both during creation and during execution. In the meantime, what about EXEC dbo.sp_helptext myProc or EXEC otherSchema.sp_helptext myProc A Show quote "Dave" <D***@discussions.microsoft.com> wrote in message news:5542B635-26C6-4D38-8965-E86D4105BD70@microsoft.com... >I create a new SQL Server login in a SQL Server 2005 database and add him >to > db_datareader and db_datawriter roles. I also grant him execute to a > stored > procedure. > > I log on as this new login. I can execute the store proc but when I try > to > view the code I get the error: > > sp_helptext myProc > > Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107 > There is no text for object 'myProc'. > > Can anyone explain why? Aaaron:
I have 4 schemas on this database: dbo.guest, sys, and information_schema. Preferencing the name of the proc with any of these schemes still gives me an error: EXEC dbo.sp_helptext myProc Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107 There is no text for object 'myProc'. BTW, this database was upgraded from a SQL 2000 database by moving and attaching the files. The old logins can run the sp_hleptext without error. It is only the new login that was created in SQL 2005 that has the problem. How can I tell the default schema for a user? Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > who is the schema of myproc? Is it dbo or something else? > > If you create stored procedures this way: > > CREATE PROCEDURE myproc > AS > .... > > You're probably going to want to stop doing that in SQL Server 2005, > especially if you do so with users that are created with a default schema > other than dbo. > > But even in 2000, it is usually considered a best practice to prefix your > objects with the owner name ... both during creation and during execution. > > In the meantime, what about > > EXEC dbo.sp_helptext myProc > or > EXEC otherSchema.sp_helptext myProc > > A > > > > > > "Dave" <D***@discussions.microsoft.com> wrote in message > news:5542B635-26C6-4D38-8965-E86D4105BD70@microsoft.com... > >I create a new SQL Server login in a SQL Server 2005 database and add him > >to > > db_datareader and db_datawriter roles. I also grant him execute to a > > stored > > procedure. > > > > I log on as this new login. I can execute the store proc but when I try > > to > > view the code I get the error: > > > > sp_helptext myProc > > > > Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107 > > There is no text for object 'myProc'. > > > > Can anyone explain why? > > > Whoops I spoke too soon.
The old users also cannot run sp_helptext myProc. Apparently only the sa can run this without error Hi,
Since it is SQL 2005 try giving VIEW DEFINITIONS permissions and try execution SP_HELPTEXT. If you still fail then try login as SA and see if you can see the text. Thanks Hari SQL Server MVP Show quote "Dave" <D***@discussions.microsoft.com> wrote in message news:5542B635-26C6-4D38-8965-E86D4105BD70@microsoft.com... >I create a new SQL Server login in a SQL Server 2005 database and add him >to > db_datareader and db_datawriter roles. I also grant him execute to a > stored > procedure. > > I log on as this new login. I can execute the store proc but when I try > to > view the code I get the error: > > sp_helptext myProc > > Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107 > There is no text for object 'myProc'. > > Can anyone explain why? Hari:
Thanks but what do you mean by "giving VIEW DEFINITIONS permissions"? BTW, I can run sp_helptext as sa, but not as this new login I created. Dave Show quote "Hari Prasad" wrote: > Hi, > > Since it is SQL 2005 try giving VIEW DEFINITIONS permissions and try > execution SP_HELPTEXT. > If you still fail then try login as SA and see if you can see the text. > > Thanks > Hari > SQL Server MVP > > > "Dave" <D***@discussions.microsoft.com> wrote in message > news:5542B635-26C6-4D38-8965-E86D4105BD70@microsoft.com... > >I create a new SQL Server login in a SQL Server 2005 database and add him > >to > > db_datareader and db_datawriter roles. I also grant him execute to a > > stored > > procedure. > > > > I log on as this new login. I can execute the store proc but when I try > > to > > view the code I get the error: > > > > sp_helptext myProc > > > > Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107 > > There is no text for object 'myProc'. > > > > Can anyone explain why? > > >  |
|||||||||||||||||||||||
Other interesting topics