My friend and former co-worker Chris Rock just posted at article over at SQLTeam.com that has a pretty good walk through of how to set up and use the VS debugger on procs. According to an MSDN article he includes it still requires sysadmin permissions to debug though, limiting it to local dev instances or a dev server at best. Why can't we have the equivalent of the alter trace added in SQL 2005 for debugging? I often complain that developers treat TSQL as if it's magic incantations instead of another kind of code, and having to debug using print statements isn't helping the situation.

Debugging Article