Home Dashboard Directory Help
Search

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


Status: 

Active


36
0
Sign in
to vote
Type: Bug
ID: 351698
Opened: 6/18/2008 4:21:15 AM
Access Restriction: Public
0
Workaround(s)
view
14
User(s) can reproduce this bug

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
---------------------------
Details
Sign in to post a comment.
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 Microsoft 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
Sign in to post a workaround.