debugging stored procedures

I have a user that is db_owner for the a database in development on a SQL 7
SP 4 server. The user is attempting to debug a stored procedure and getting
the error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
I saw this error listed on the support site at
http://support.microsoft.com/defaul...3&Product=sql2k
. The site says:
This behavior is a design change in SQL Server 2000 SP3 to enhance security.
This design change includes the following changes:
a.. A database users can only step into stored procedures that they own.
b.. A database owner (DBO) can debug any stored procedure in the database
that the DBO owns. (A DBO owns the database and, therefore, all its stored
procedures.)
c.. Members of the SysAdmin server role can debug any stored procedure in
any database on the server. (A member of the SysAdmin server role owns the
server and, therefore, all its databases.)
For more information about Transact-SQL Debugging, see the "Using
Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
topics in SQL Server Books Online.
Does the user need to be the dbo ( creator) of the database to get the
debugger to work and not just a member of db_owner role (this does not
appear to work)? Is there a work around so that my user can debug his
stored procedures without me having to debug every stored procedure for the
several development servers in house?Hi,
You need to add the same user in Master database and then grant Execute
permission to that user on SP_SDIDEBUG procedure.
Thanks
Hari
MCDBA
"Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
> I have a user that is db_owner for the a database in development on a SQL
7
> SP 4 server. The user is attempting to debug a stored procedure and
getting
> the error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> I saw this error listed on the support site at
>
http://support.microsoft.com/defaul...3&Product=sql2k
> . The site says:
> This behavior is a design change in SQL Server 2000 SP3 to enhance
security.
> This design change includes the following changes:
> a.. A database users can only step into stored procedures that they own.
> b.. A database owner (DBO) can debug any stored procedure in the
database
> that the DBO owns. (A DBO owns the database and, therefore, all its stored
> procedures.)
> c.. Members of the SysAdmin server role can debug any stored procedure
in
> any database on the server. (A member of the SysAdmin server role owns the
> server and, therefore, all its databases.)
> For more information about Transact-SQL Debugging, see the "Using
> Transact-SQL Debugger" and "Troubleshooting the Transact-SQL Debugger"
> topics in SQL Server Books Online.
> Does the user need to be the dbo ( creator) of the database to get the
> debugger to work and not just a member of db_owner role (this does not
> appear to work)? Is there a work around so that my user can debug his
> stored procedures without me having to debug every stored procedure for
the
> several development servers in house?
>
>|||Thanks for the input. That is the answer I already had. I was hoping there
was a less granular way to apply those permissions.
I set up a role for the debugging in the master database and assigned the
users to that.
Thanks again.
Stacy Hein
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eMUInmR8DHA.360@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You need to add the same user in Master database and then grant Execute
> permission to that user on SP_SDIDEBUG procedure.
> Thanks
> Hari
> MCDBA
> "Stacy Hein" <sthein5@.rockwellcollins.com> wrote in message
> news:ePOiKaO8DHA.3360@.tk2msftngp13.phx.gbl...
SQL
> 7
> getting
> on
>
http://support.microsoft.com/defaul...3&Product=sql2k
> security.
own.
> database
stored
> in
the
> the
>