Some of our developers regularly debug stored procedures in SQL Server 2000. In fact, they can't live without it. The next version of their application will use SQL Server 2005.
Today, one of the developers asked me how to use SQL Debugging in SQL Server 2005. I barely even know how to use it SQL Server 2000, but I wanted to help him so I started reading about it on MSDN.
In SQL Server 2000, I had to grant execute permission on master.dbo.sp_sdidebug extended stored procedure in order for them to debug T-SQL code. According to this article, the connection account must be a member of the sysadmin role in order to use SQL Server 2005 debugging.
Our developers are members of the db_owner fixed database role in their user database. They are not members of the sysadmin fixed server role.
This a big problem for us. I am hoping that there is a workaround, although I haven't found one yet.
Please leave me a comment if you know of a way for a non-sysadmin user to debug stored procedures in SQL Server 2005.