8

I am working on SQL Server 2008 R2 Express, when I am using SQL Server debugging feature from client pc, this error occurs:

The EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

My user name is 'HALI' and permission is assigned to me is 'public' and 'db_Owner',

now after this error I have assigned all permissions available. And all server roles.

Now at this time I got new error message,

Error HRESULT E_FAIL has been returned from a call to a COM component. (mscorlib)

Now what the solution would be for this error.

Sukhdeep Singh
  • 50
  • 1
  • 7
Haider Ali Wajihi
  • 2,756
  • 7
  • 51
  • 82

3 Answers3

7

I am not sure if disabling firewall is enough. if the user has sysadmin permissions and still getting this error means client is not able to connect to server in debugging mode. check this TCP and UDP ports are correctly open.

SQL remote debugging configuration

EDIT:1

I am sure you must have also googled a lot and tried to find out about the error messages. What I have found so far is the Error in the OP you mentioned is misleading and from that error, we cannot tell what exactly is wrong or what to look next. But as you replied in the comments, after making changes to enable Remote Debugger; the mentioned error is more clear and I feel that proper permission is still an issue. Plenty of other suggestion you also might have tried so far, but if you haven’t done this, let's try one more time:

In one of the comments I mentioned you to try login using the windows user.

Now Keep the settings you already did for TCP ports, Firewall Exception, etc… explained in the above link. you have windows 2012 server where you installed DB server. and windows 8 Prof where you have DB client and you connecting through SSMS. Now I believe both machines are in the same domain. Let’s say domain XYZ. You must have windows login on server, let’s say it is “XYZ\HALI” using which you can login to the Windows Server. log in and make sure that login also exists in SQL SERVER with sysadmin permission. Because client machine is also in same domain make sure you have logged in into the client machine using same user “XYZ\HALI”. Now start SSMS and choose windows authentication instead of SQL SERVER authentication. Try to start Debugging T-SQL code now.

if client and server machines are not in the same domain then we have to register server name on client machine as Linked Server, impersonate the login/user as local and then try the debugging.

Edit:2

Login to SSMS using Windows Authentication

recnac
  • 3,744
  • 6
  • 24
  • 46
Anup Shah
  • 1,256
  • 10
  • 15
  • after doing all struff mentioned in blog, both side, server and client, when i tried to debug on clinet this error occured, before making firewall exception i was getting different error, now i am getting this error - . . Unable to start the Transact-SQL debugger, could not connect to the Database Engine instance ‘xxxx’. Make sure you have enabled the debugging firewall exceptions and are using a login that is a member of the sysadmin fixed server role. Click Help for more information. – Haider Ali Wajihi Oct 26 '13 at 11:53
  • what OS is on server and client. also when you connect to SSMS on client you use "servername\instancename" not IP address right. also you using "windows authentication user" which exists on both machine. – Anup Shah Oct 26 '13 at 14:40
  • yes on client i m connecting with server name, and i am using sql authentification, should i use ip address – Haider Ali Wajihi Oct 27 '13 at 05:56
  • no, "servername\instancename" is good. just thinking can you try with windows user that exists on both server and client. I am wondering the sql server user you using might be having permission issue. – Anup Shah Oct 27 '13 at 06:29
  • on server we have OS "windows server 2012 data center" and on client we have "windows 8 pro" – Haider Ali Wajihi Oct 28 '13 at 10:42
  • check the Edit1 under same answer. – Anup Shah Oct 29 '13 at 03:35
  • when i am trying to login with windows credential thi message happens Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452) – Haider Ali Wajihi Oct 29 '13 at 13:15
  • if you are doing it right and i am sure you must be yet see the image that shows how I would loging to SQL server using windows credential. otherwise your client and machine and Server machine are not in same domain. and hence to work with windows authentication as i mentioned we need to impersonate that user using linked server. I am not 100% confident to use the SQL Server Authentication as that SQL user will require some system permissions on the Server machine as well that I am not aware of. if you have team-viewer on system and would like to debug let me know. – Anup Shah Oct 29 '13 at 14:45
3

I encountered this problem after generating a SQL 2012 script and running it in a SQL 2008 R2 database.

After some research, I found out that my target SQL 2008 R2 was running on a 32-bit OS which supports a file of up to 2GB in size. So I saved the script as C:\myScript.sql and successfully executed it in a command prompt with these steps:

  1. Open a command prompt window.

  2. In the command prompt window, type:

    sqlcmd -S myServer\instanceName –U yourUserName –P yourPassword -i C:\myScript.sql

    where myServer\instanceName is the instance name. Supply the user name and password for SQL server login, and finally the path of the file as indicated.

  3. Press Enter.

References:

Pang
  • 9,564
  • 146
  • 81
  • 122
Marto
  • 31
  • 4
1

The few times I've run across that error, it's always been firewall related. Are you working on a remote server, or your local machine? (Parenthetically, be careful assigning all possible roles. Some of them are ones you absolutely don't want. The one you need for SQL debugging is sysadmin - try disabling the rest of the roles you checked off and just assigning that one.)

Debugging procs is always a pain. This ASP.NET post helped me out substantially awhile ago.

antinescience
  • 2,339
  • 23
  • 31