Problem ovewriting xp_sendmail

I am trying to overwrite xp_sendmail by a transact sql stored procedure. I delete the original one and write a new one with the same name but I keep receiving errors which seems to relate to the orinal one as if the security is kept in place even though I deleted the extended procedure.

Here are the details of what I did:

I created a stored procedure xp_sendmail in master database.

I called the procedure and receives the following error message:

Msg 15281, Level 16, State 1, Procedure xp_sendmail, Line 1

SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

I tried deleting the extended stored procedure with the same name from the visual interface of "Microsoft ssql server management studio". The process did not work, I received the following message: "Cannot use SP_DROPEXTENDEDPROC or DBCC DROPEXTENDEDPROC with xp_sendmail because "xp_sendmail" is a procedure. Use DROP PROCEDURE (MSSQL ERROR 3751)"

I believe the interface does not do the distinction between my procedure and the orinal extended procedure. I therefore tried the following:

drop the stored procedure I wrote.

drop once more the extended procedure. I received a different message: "Cannot drop procedure because it does not exists or you do not have the permission (mssql error 3701)"

I do refresh, I reopen the management studio, no matter, the extended procedure is still there. Only way I find to delete it from the interface is recreating from transact sql before redropping it.

sp_addextendedproc 'xp_sendmail', [the path of the dll here]

Even aftter doing all those and recreating my own procedure, I still see the extended procedure reapearing in the sql server management studio interface and I still receive the security error.

It is as if sql server fails to notice I dropped the procedure.

I am trying all those with the sa user so I doubt the issue comes from the fact that I lack permission.

Is there something that can be done to force sql server to consider my procedure as a separate one that the original one. Why is the extended procedure still appearing and why do I still receive security error after I drop it. I tried calling the procedure without with both the extended procedure and transact sql stored procedure dropped or with the extended one droped and the other one present, still I get no success and still receive the security error. I even tried freeing the dll from memory but it makes no difference:

DBCC xp_sendmail (free)

Thanks for the help.

I am really sorry to say this, but unfortunately this is an unsupported scenario and we will not be able to help.

My only recommendation at this point is to try to backup any important data from your system, and reinstall SQL Server. Once you have a clean system, create your XP under a different name (i.e. xp_sendmail2)

I strongly recommend using only the supported mechanisms designed to extend the system (such as creating new XPs, CLR assemblies, etc.) instead of trying to modify the system objects.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks for your time.

the reason we try to do this is because we have a bunch of application calling xp_sendmail and want to move to a mail sending method which allows to to define the smtp server address to enforce an email policy.

It is therefore easier to just overwrite the xp_sendmail than to overwrite all our applications to call another procedure. This used to work in sql server 2000.

That said, I don't think it is necessary to rebuild the server. I can run sp_addextendedproc to rerister the dll and it works. All I need to find is how to tell sql server to forget that this extended procedure existed. Even if it is deleted, it is still visible in the management studio interface and my procedure gets mixed up with the extended procedure when it comes to permission and settings.

If this cannot be achieved, I'll move to plan B. However, if anybody knows of a method to resolve this issue, then it would be appreciated.

Thanks again.