MSIT-MSO: Debugging SQL Query fails with an error The EXECUTE permission was denied on the object 'sp_enable_sql_debug' - by Srikj

Status : 

 


46
0
Sign in
to vote
ID 351698 Comments
Status Active Workarounds
Type Bug Repros 18
Opened 6/18/2008 4:21:15 AM
Access Restriction Public

Description

When i connected to a server on which i have read-only access, and tried to start debugging; debugging failed with the following error. This makes user clueless what kind of access he needs as  'sp_enable_sql_debug' is not documented.

 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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1442&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


And subsequently it errored out with 

---------------------------
Microsoft SQL Server Management Studio
---------------------------
Unable to start program 'MSSQL:://msorguat/master/sys/=0'.  
---------------------------
OK   
---------------------------
Sign in to post a comment.
Posted by Jay Sanati on 8/28/2015 at 11:39 AM
--> The minimum security requirement is sysadmin. This is by design from SQL engine security.

So this is a BAD design.
Developers on development environment should be able to debug .if this is a limitation you should find a way to solve it, otherwise the feature will be useless for huge number of developers in a secure environment.

Thank you.

Posted by Srinivasan Srisailam on 7/18/2014 at 1:13 AM
In case of production server this is acceptable that no application user should debug the query on production but in my case even for Development server if the application needs to test it they needs a sysadmin role which is not possible because our environment is shared server and if sysadmin role is granted they can access any database with full rights........

In MSSQL server always many things are covered with sysadmin role and administrator always have a lower edge here because of SQL server security architecture.
Posted by NikiR on 8/30/2012 at 8:54 AM
> The minimum security requirement is sysadmin. This is by design from SQL engine security.

Hmm...
You are ensuring that professional developers can't use the debugging, because no dba in their right mind is going to give all developers sysadmin security.
It might be by design, but it is an AWFUL design that still hasn't been rectified almost a decade after its appalling inception.
Posted by Toni2 on 9/6/2011 at 2:21 PM
I have a user who has read-only access to a database and is not trying to debug anything. She is simply trying to run a select query and receives the 'execute permission was denied on the object sp_enable_sql_debug, database mssqlsystemresource, schema sys' error.

Why would this user need to be debugging anything at that point?
This is from SQL Server Management Studio 2008 Express connecting to either a SQL Server 2005 sp3 or SQL 2005 sp4 or even SQL 2008 R2 sp1 server.

What can be done to work around this issue?
Posted by eduardomilen on 3/18/2011 at 8:17 AM
you need to grant execute permissions on the procedure 'sp_enable_sql_debug' in the master database or in the manner recommended against granting permission for the owner user in the master database
Posted by David DeLella on 1/24/2011 at 10:05 AM
I agree with the users. sysadmin should be reserved for administration and contains to many rights for a standard developer who needs debugging capability. These are two clearly separate roles and should be treated as such.
Posted by Bryan St on 9/9/2010 at 6:58 PM
I agree with the last comment. If a developer in my shop wants to debug their code in their own database, I shouldn't be forced to give them the ability to 'DROP DATABASE master' in order to accomplish that goal.

Consider this a request for Microsoft to add something, a server role, a database role, or some other facility to grant a user permissions required to run the Debug facility in Management Studio.
Posted by Karen Wallace on 4/23/2010 at 11:44 AM
How is that by design? Security best practices most definitely do NOT recommend making everyone an sysadmin.
Posted by Eric [MSFT] on 6/18/2008 at 10:06 AM
Srikantj,

The minimum security requirement is sysadmin. This is by design from SQL engine security.

Regards,
Eric