May 20, 2016 at 11:06 am
I'm trying to debug a stored procedure I wrote for a SSRS report. I've set the breakpoint in SSMS, then I click on Debug. Then I get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to start debugger
------------------------------
ADDITIONAL INFORMATION:
The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
I've asked the DBA to give me the necessary privileges in the relevant server and database, but he said he can't find sp_enable_sql_debug.
How do I proceed?
Kindest Regards, Rod Connect with me on LinkedIn.
May 20, 2016 at 11:27 am
I believe you need to be a member of the sysadmin role in order to use debugging.
May 20, 2016 at 10:43 pm
The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
to fix this error create a new database role
CREATE ROLE db_executor;
then grant that role exec permission.
GRANT EXECUTE TO db_executor;
May 22, 2016 at 7:19 am
johnwalker10 (5/20/2016)
The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
to fix this error create a new database role
CREATE ROLE db_executor;then grant that role exec permission.
GRANT EXECUTE TO db_executor;
Might it then be possible for me to debug my stored procs without having to be a part of the sysadmin role. I know there's no way they'll give me sysadmin privileges.
Kindest Regards, Rod Connect with me on LinkedIn.
May 23, 2016 at 9:22 am
Yes
May 23, 2016 at 3:53 pm
I've been added to the db_owner role in that database. It's our understanding that would be more than sufficient in order to make it possible for me to debug stored procedures. But I still get the same error whenever I set breakpoints in a SP and click on the Debug button in SSMS.
The way I see it, either (a) we've got a misunderwstanding of what db_owner can do, or (b) something else is going on.
Kindest Regards, Rod Connect with me on LinkedIn.
May 24, 2016 at 9:24 am
Pretty sure it requires sysadmin.
There's an active Connect item complaining about this: https://connect.microsoft.com/SQLServer/feedback/details/351698/msit-mso-debugging-sql-query-fails-with-an-error-the-execute-permission-was-denied-on-the-object-sp-enable-sql-debug.
The Microsoft engineer who responded initially said:
The minimum security requirement is sysadmin. This is by design from SQL engine security.
We can upvote the Connect item, but given how long it's been since a response from MS, I wouldn't hold my breath.
Cheers!
May 24, 2016 at 10:04 am
Jacob Wilkins (5/24/2016)
Pretty sure it requires sysadmin.There's an active Connect item complaining about this: https://connect.microsoft.com/SQLServer/feedback/details/351698/msit-mso-debugging-sql-query-fails-with-an-error-the-execute-permission-was-denied-on-the-object-sp-enable-sql-debug.
The Microsoft engineer who responded initially said:
The minimum security requirement is sysadmin. This is by design from SQL engine security.
We can upvote the Connect item, but given how long it's been since a response from MS, I wouldn't hold my breath.
Cheers!
FWIW, I've added my up vote to that Connect item.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply