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.