sql server programming: permission denied on object 'sp_sdidebug', database 'dbname', -- sql server programming

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

Alejandro Mesa
2/28/2005 4:49:04 PM

sql server programming: Jeff,

Why do you need to GRANT execute on this sp?

AMB

[quoted text, click to view]

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

Jeff Kish
2/28/2005 6:38:03 PM

Hi.
I have seen lots of discussion.
My starting problem was that after installing sql server 2000 and sp3, I could
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.

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

Jeff Kish
3/1/2005 9:34:59 AM

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"
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.