SQL Debugging in SQL Server 2005
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.
Legacy Comments
|
Tom Resing
2006-06-13 |
re: SQL Debugging in SQL Server 2005 Maybe you could consider allowing this role for developers on a non-production machine. SQL Server Express is much easier to use, maybe some amount of testing can happen on developer workstations with a subset of the production data. |
|
Tara
2006-06-13 |
re: SQL Debugging in SQL Server 2005 Yes that would be one solution, however it goes against our policy. So I have come up with a slower alternative solution. The solution would be for them to run a backup of the development database specifying a remote location in the FROM, then doing a local restore on their machines. They have sysadmin on their own boxes, so now they would just need to point their connection strings over. They might even be able to debug there for quite some time until they need to pull new data and code over. It's a slow solution, but at least it doesn't violate our policy. |
|
Ray
2006-06-23 |
re: SQL Debugging in SQL Server 2005 In BOL it says not to use the debugger on a Production database: "It is not advisable to use the Transact-SQL debugger on a production server. While in step execution mode, the debugger can lock certain system resources that are needed by other processes." Have you run into any issues running it in production? Thanks, Ray |
|
Tara
2006-06-23 |
re: SQL Debugging in SQL Server 2005 I never run it in production. If we need to debug production, we copy the prod databases over to dev. |
|
Mladen
2006-07-01 |
re: SQL Debugging in SQL Server 2005 Some usefull info here: http://blogs.msdn.com/sqlprogrammability/archive/2006/ 07/01/653040.aspx |