Question: When running SQL Debugging I get this SQL error. How can I fix?
The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Answer: SQL Debugging requires sysadmin permission. This is unfortunate for DBAs , as they are forced to give developers sysadmin privileges, which usually is against most SQL Server Security Policies.
As a rule I don’t give developers Sysadmin rights . There normally has to be a strong case for sysadmin rights
As a note from Microsoft
“We recommend that you debug Transact-SQL code on a test server, not a production server, for the following reasons: Debugging is a highly privileged operation. Therefore, only members of the sysadmin fixed server role are allowed to debug in SQL Server. Debugging sessions often run for long periods of time while you investigate the operations of several Transact-SQL statements. Locks, such as update locks, that are acquired by the session might be held for extended periods, until the session has ended or the transaction is committed or rolled back.”