-
Title
Debug Error "The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database ' -
Description
When trying to debug on a remote server, the following error occurs "The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'."
-
Cause
Permission issue
-
Resolution
Grant user sysadmin role in order to debug. This is a SQL Server debugging requirement. For more information regarding debug requirements, please see the release notes or below:
Configure Debugging for SQL Server 2008
To debug locally, perform the following on the Toad client
1. Install the SQL Server 2008 Client Tools on the Toad client.
2. If autocommit is disabled, commit or rollback the connection in Toad before starting the debugging session.
To debug remotely, perform the following
1. Install the SQL Server 2008 Client Tools on the Toad client.
2. Make sure the following permissions are set:
o Your Windows account that Toad is running under must be a member of sysadmin server role in the SQL Server being debugged.
o Your server login must also be a member of the sysadmin role. This login can be your Windows user account (Windows authentication), or a SQL Server login (SQL Server authentication).
To create a server login from your Windows account and add it to the sysadmin role, a user who already has sysadmin privileges must execute the following:
sp_addsrvrolemember 'Domain\Name', 'sysadmin'
where Domain\Name is your Windows login.
3. If autocommit is disabled, commit or rollback the connection in Toad before starting the debugging session.
Configure Debugging for SQL Server 2005
To debug locally, perform the following on the Toad client:
1. Install the SQL Server 2005 Workstation Component option of the SQL Server 2005 installation.
2. In Toad, commit or rollback the connection before starting the debugging session.
To debug remotely, perform the following:
1. On the Toad client, do one of the following:
• Install the SQL Server 2005 Workstation Components option of the SQL Server 2005 installation.
• Copy and register the ssdebugps.dll (debugging host component) on both the client and server. This file is located by default in the following directory on the server where SQL Server 2005 is installed:C:\Program Files\ Common Files\Microsoft Shared\SQL DebuggingTo register this file, open a command prompt and enter the following command: regsvr32 ssdebugps.dllNote: If you are unable to locate this file, you can run the remote debugging setup component (rdbgsetup.exe). This file is located by default in the following directory on the server where SQL Server 2005 is installed: C:\Program Files\Microsoft SQL Server\90\Shared\1033
2. Make sure the following permissions are set:
• Your Windows account that Toad is running under must be a member of sysadmin server role in the SQL Server being debugged.
• Your server login must also be a member of the sysadmin role. This login can be your Windows user account (Windows authentication), or a SQL Server login (SQL Server authentication).To create a Server login from your Windows login and add it to the sysadmin role, a user who already has sysadmin privileges must execute the following:sp_addsrvrolemember 'Domain\Name', 'sysadmin'where Domain\Name is your Windows loginNote: By default, Windows administrators on a machine running SQL Server are SQL Server sysadmins on that machine.
3. In Toad, commit or rollback the connection before starting the debugging session.
Configure Debugging for SQL Server 2000
To debug locally, perform the following on the Toad client:
1. Make sure that SQL Server 2000 with SP3 or SP4 is installed.
2. In Toad, commit or rollback the connection before starting the debugging session.
To debug remotely, perform the following:
1. On the Toad client, do one of the following:
• Make sure that SQL Server 2000 with SP3 or SP4 is installed.
• Copy and register the sqldbg.dll and sqldbreg2.exe (debugging host components) on both the Toad client and server. These files are located by default in the following directory on the server where SQL Server 2000 is installed:C:\Program Files\Common Files\Microsoft Shared\SQL DebuggingTo register these files, open a command prompt and enter the following commands:regsvr32 sqldbg.dll
sqldbreg2.exe /RegServer
Note: The Remote Procedure Call (RPC) service should be started on both the Toad client and server.
2. Make sure the following permissions are set:
• Your Windows account that Toad is running under must be a member of the sysadmin server role for the instance you are debugging.
• Your server login must also be a member of the sysadmin role. This login can be your Windows user account (Windows authentication), or a SQL Server login (SQL Server authentication).To create a server login from your Windows account and add it to the sysadmin role, a user who already has sysadmin privileges must execute the following:sp_addsrvrolemember 'Domain\Name', 'sysadmin'where Domain\Name is your Windows login.
• DCOM Access permissions must be granted for the system account and the group or user who needs access.To verify permissions:
a. Open a command prompt and run dcomcnfg.exe.
b. Expand Component Services, Computers.
c. Right-click My Computer and select Properties.
d. Select the COM Security tab.
e. Under Access Permissions, click Edit Default, and verify permissions.
Note: By default, Windows administrators on a computer running SQL Server are SQL Server sysadmins on that computer.
3. In Toad, commit or rollback the connection before starting the debugging session.