SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server - User Could Not Execute Stored Procedure sp_enable_sql_debug

13 September,2012 by Jack Vamvas

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.”

Related Posts

SQL Server – How to troubleshoot query timeouts

SQL Server - ALTER TRACE and developers

SQL Server Security Policy


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer