permission denied on object 'sp_sdidebug', database 'dbname',

Hi.
I have seen lots of discussion.
My starting problem was that after installing sql server 2000 and sp3, I cou
ld
not debug stored procedures.
I thought I saw the solution in MSDN, but
it did not work for me (I could not grant execute
on sp_sdidebug to my user).
short end is.. I had to do something else.
I'm developing on a local mssqlserver 2000 sp3
windows 2000 is the o/s
I logged in as the sp and ran this:
EXECUTE sp_sdidebug 'LEGACY_ON'
Then I tried to enter this:
GRANT EXECUTE
ON sp_sdidebug
TO MYUSER
But I got an error about not being able to set permissions for objects owned
by others.
I'm obviously clueless, but I did find a blurb from someone who said they
had to add the users to the MASTER database as users with db_owner checked.
After I did this, I could debug sp's, but still could not do the 'GRANT
EXECUTE ON sp_sdidebug'.
So
1 - what is the best way to get the (EXECUTE sp_sdidebug 'LEGACY_ON')
executed each time the db starts up? (if I need to put it in some sp, how
exactly do I get that called at startup?)
2 - any idea what I'm boffing here to prevent the 'GRANT EXECUTE ON
sp_sdidebug' from working?
3 - any thoughts about the MASTER.db_owner "solution"?
Thanks for your patience as I am sure this is a well trampled ground.
Jeff KishJeff,
Why do you need to GRANT execute on this sp?
AMB
"Jeff Kish" wrote:

> Hi.
> I have seen lots of discussion.
> My starting problem was that after installing sql server 2000 and sp3, I c
ould
> not debug stored procedures.
> I thought I saw the solution in MSDN, but
> it did not work for me (I could not grant execute
> on sp_sdidebug to my user).
> short end is.. I had to do something else.
> I'm developing on a local mssqlserver 2000 sp3
> Windows 2000 is the o/s
> I logged in as the sp and ran this:
> EXECUTE sp_sdidebug 'LEGACY_ON'
> Then I tried to enter this:
> GRANT EXECUTE
> ON sp_sdidebug
> TO MYUSER
> But I got an error about not being able to set permissions for objects own
ed
> by others.
> I'm obviously clueless, but I did find a blurb from someone who said they
> had to add the users to the MASTER database as users with db_owner checked
.
> After I did this, I could debug sp's, but still could not do the 'GRANT
> EXECUTE ON sp_sdidebug'.
>
> So
> 1 - what is the best way to get the (EXECUTE sp_sdidebug 'LEGACY_ON')
> executed each time the db starts up? (if I need to put it in some sp, how
> exactly do I get that called at startup?)
> 2 - any idea what I'm boffing here to prevent the 'GRANT EXECUTE ON
> sp_sdidebug' from working?
> 3 - any thoughts about the MASTER.db_owner "solution"?
> Thanks for your patience as I am sure this is a well trampled ground.
> Jeff Kish
>|||When I installed ms sql server with sp3 I found I could not run the debug
on stored procedures. It was grayed out.
Once I did some research, I determined (I "think" from MSDN) that
I needed to GRANT to run the debugger logged in as this user.
I got an error doing the GRANT. Looking around a bit more, I saw that
I also needed to run in legacy mode for this object.
I submitted a script for that, but I still could not GRANT.
After substantially more investigation, I saw one user was able to get the
debugger running if he added his user as a db_owner to the master
database.
After I did this, I still could not do the GRANT, but was able to run the
debugger on stored procedures.
Now I'm trying to figure out if what I did was a reasonable solution, and
why I could not do the GRANT. Finally, I'm casting about for an explanation
and some level of confidence for what I did to "fix" the problem.
Also, I wanted to figure out how to run the LEGACY script at startup so I
don't need to do it every time.
Thanks
On Mon, 28 Feb 2005 16:49:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>Jeff,
>Why do you need to GRANT execute on this sp?
>
>AMB
>
>"Jeff Kish" wrote:
>
Jeff Kish