EXECUTE permission denied on object 'sp_sdidebug', database 'master', owner 'dbo' [from ASP.NET]

[CJM]

Probably slightly off-topic for these NGs, and multi-posted [sorry - also
posted in .dotnet.aspnet]...

I'm getting a problem debugging a simple-ish page in ASP.NET:

Exception Details: System.Data.SqlClient.SqlException: EXECUTE permission
denied on object 'sp_sdidebug', database 'master', owner 'dbo'.

It occurs on a SQLDataAdapter.Fill instruction. I suspect it is an ASP.NET
debugging issue, but for all I know it could be an SQL Server issue.....

Is there something I need to configure in SQL Server (2000) to enable apps
like ASP.NET to debug?

Thanks

Chris

[SriSamp]

What is the user that is configured to access SQL Server?? Check whether
this user can perform this operation.
--
HTH,
SriSamp
Please reply to the whole group only!

"CJM" <cjm...@yahoo.co.uk> wrote in message
news:#6xkE3IG...@tk2msftngp13.phx.gbl...

[CJM]

The user specified in the connection string is a dbo, and the page pulls the
data out correctly.

It is when I try and run the page in debug mod that it complains about the
SQL permissions...

"SriSamp" <ssam...@sct.co.in> wrote in message
news:Om8xR$IGDHA...@TK2MSFTNGP12.phx.gbl...

[Jacco Schalkwijk]

There are no permissions granted on sp_sdidedebug by default, so you have to
be a member of the System Administrators server role to be able to execute
it, being dbo is not enough. You can grant permissions to other logins on
sp_sdidedebug though.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

"CJM" <cjm...@yahoo.co.uk> wrote in message

news:OTydqSJG...@TK2MSFTNGP12.phx.gbl...

[Cristino Floresca]

Looks like the user you are connecting to the database doesn't have
appropriate permission to execute the command. You can give the user you are
logging in with a db_owner permission and it should be okay.

"CJM" <cjm...@yahoo.co.uk> wrote in message

news:%236xkE3I...@tk2msftngp13.phx.gbl...

[CJM]

Jacco,

I give the Public group execute permissions to Master\sp_sdidebug via
Enterprise Mgr and it works [wayhey!].

However, I suspect that this is a very liberal approach in terms of
security(!); is there a better way of going about this? I couldnt see a way
of just giving one or two users the appropriate rights...

Also, this seems a bit of a ball-ache! I'd have thought there was a cleaner
and simpler (ie automated) way of doing all this; have I missed something
along the way?

Thanks

"Jacco Schalkwijk" <NOSPAM...@eurostop.co.uk> wrote in message
news:OBlBdcJ...@TK2MSFTNGP12.phx.gbl...

[Jacco Schalkwijk]

"CJM" <cjm...@yahoo.co.uk> wrote in message

news:#Wl#22JGDH...@TK2MSFTNGP10.phx.gbl...

> Jacco,
>
> I give the Public group execute permissions to Master\sp_sdidebug via
> Enterprise Mgr and it works [wayhey!].
>
> However, I suspect that this is a very liberal approach in terms of
> security(!); is there a better way of going about this? I couldnt see a
way
> of just giving one or two users the appropriate rights...

Depends. If your server is a development server to which only a limited
number of developers have access in the first place it won't be much of a
problem. You should never give permissions on sp_sdidebug on a production
server.

To grant separate logins permissions on the stored procedure you have to add
them as users to the master database first. If logins access the master
database and they dare not associated with a user account they will use the
guest user instead, and guest is a member of the public database role in
master. That is how your current solution works, very liberal security
indeed :-).

Btw, make sure you don't confuse (Windows) users with (SQL Server) logins or
(database) users, or (Windows) groups with (sql) server roles or (database)
roles. They are all different and if you use the incorrect terminology
things can get very confusing.

>
> Also, this seems a bit of a ball-ache! I'd have thought there was a
cleaner
> and simpler (ie automated) way of doing all this; have I missed something
> along the way?

Nope, security is a bit of trouble, and sp_sdidebug is an extended procdure
that digs quite deep into SQL Server internals, so it is best protected
well.

[CJM]

Yes this is indeed for my development machine (ie its a standalone XPSP1
rather a win2k server), but I still dont like giving away more than I have
to, so I'll set up one SQL account to execute the SP rather than Public.

Thanks for your help.

Chris