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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy