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/default.aspx?scid=kb;en-us;328173&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?I hope you have DB_DDLAdmin permissions on the database,
Just add your user account in the master database and
grant em Execute permissions to SP_SDIDEBUG system
procedure
HTH
Saleem Hakani
>--Original Message--
>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/default.aspx?scid=kb;en-
us;328173&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/default.aspx?scid=kb;en-us;328173&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...
> > 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/default.aspx?scid=kb;en-us;328173&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?
> >
> >
> >
>