Thursday, March 29, 2012

Dropping Extended Stored Proc

How do you delete a extended stored procedure in SQL 2005?sp_dropextendedproc
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:2605012C-28F4-4822-ACA6-EC63C94A7C78@.microsoft.com...
> How do you delete a extended stored procedure in SQL 2005?|||use
sp_dropextendedproc
see following link for more detail
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
vinu
"John Oberlin" wrote:
> How do you delete a extended stored procedure in SQL 2005?|||I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
in SQL 2005 because of this comment in
http://msdn2.microsoft.com/en-us/library/ms164755.aspx
"In SQL Server 2005, sp_dropextendedproc does not drop system extended
stored procedures. Instead, the system administrator should deny EXECUTE
permission on the extended stored procedure to the public role. In SQL Server
2000, sp_dropextendedproc could be used to drop any extended stored
procedure. "
John|||The documentation is pretty clear on the subject. Don't use this proc to drop *system* extended
procs. For this, DENY execute permissions instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:B90D7CC5-D4CC-4D2A-B75A-445E40AF6074@.microsoft.com...
>I am familiar with sp_dropextendedproc in SQL 2000. But I didn't even try it
> in SQL 2005 because of this comment in
> http://msdn2.microsoft.com/en-us/library/ms164755.aspx
> "In SQL Server 2005, sp_dropextendedproc does not drop system extended
> stored procedures. Instead, the system administrator should deny EXECUTE
> permission on the extended stored procedure to the public role. In SQL Server
> 2000, sp_dropextendedproc could be used to drop any extended stored
> procedure. "
> John
>|||plus...sp_dropextendedproc can be run only in the master database and the
extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
Any help would be greatly appreciated.
Thanks,
John|||Extended procedures can only live in master. I checked my 2005 installation, and I have an
xp_sendmail in master and none in msdb. If you have something called xp_sendmail in msdb, then it is
a regular stored procedure, not an extended stored procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:EE66AD24-622F-4DE8-971C-DAFD43C286D0@.microsoft.com...
> plus...sp_dropextendedproc can be run only in the master database and the
> extended stored proc I am dropping, in this case xp_sendmail, is in msdb.
> Any help would be greatly appreciated.
> Thanks,
> John
>|||xp_sendmail is a system extended stored proc in master. Is there a way to
delete it? Or alternatively, is there a way to alter it?
Thanks,
John
P.S. my apologies, it is sp_send_dbmail that is in msdb

No comments:

Post a Comment