大家好!由于SQL2005中有很多的存储过程我都用不到所以想删,于是我在网上找到了一些SQL删除储存过程的语句,大多都是这样的:use master EXEC sp_dropextendedproc 'xp_cmdshell' EXEC sp_dropextendedproc 'Sp_OACreate' EXEC sp_dropextendedproc 'Sp_OADestroy' EXEC sp_dropextendedproc 'Sp_OAGetErrorInfo' EXEC sp_dropextendedproc 'Sp_OAGetProperty' EXEC sp_dropextendedproc 'Sp_OAMethod' EXEC sp_dropextendedproc 'Sp_OASetProperty' EXEC sp_dropextendedproc 'Sp_OAStop' EXEC sp_dropextendedproc 'Xp_regaddmultistring' EXEC sp_dropextendedproc 'Xp_regdeletekey' EXEC sp_dropextendedproc 'Xp_regdeletevalue' EXEC sp_dropextendedproc 'Xp_regenumvalues' EXEC sp_dropextendedproc 'Xp_regread' EXEC sp_dropextendedproc 'Xp_regremovemultistring' EXEC sp_dropextendedproc 'Xp_regwrite' drop procedure sp_makewebtask但是我在SQL2005的管理器中执行的时候,它却这样提示
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'xp_cmdshell', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OACreate', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OADestroy', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAGetErrorInfo', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAGetProperty', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAMethod', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OASetProperty', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAStop', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regaddmultistring', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regdeletekey', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regdeletevalue', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regenumvalues', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regread', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regremovemultistring', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regwrite', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 33
Cannot drop the procedure 'sp_makewebtask', because it does not exist or you do not have permission.
注:我是使用SA权限登陆SQL2005管理器的!不知道哪里出了问题!
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'xp_cmdshell', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OACreate', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OADestroy', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAGetErrorInfo', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAGetProperty', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAMethod', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OASetProperty', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Sp_OAStop', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regaddmultistring', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regdeletekey', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regdeletevalue', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regenumvalues', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regread', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regremovemultistring', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Procedure sp_dropextendedproc, Line 16
Cannot drop the procedure 'Xp_regwrite', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 33
Cannot drop the procedure 'sp_makewebtask', because it does not exist or you do not have permission.
注:我是使用SA权限登陆SQL2005管理器的!不知道哪里出了问题!
在 SQL Server 2000 中,sp_dropextendedproc 可用于删除任何扩展存储过程。
SQL code
-- 1. 得到 master 库的目录位置(记下这个目录)
SELECT
LEFT(physical_name, LEN(physical_name) - 10)
FROM master.sys.database_files
WHERE type = 0
GO-- 2. 操作系统中打开上述目录,找到如下两个文件
mssqlsystemresource.mdf
mssqlsystemresource.ldf将他们复制一份,改名为:
_mssqlsystemresource.mdf
_mssqlsystemresource.ldf
GO-- 3. 回到 sql server, 执行下面的语句(语句中的目录要改成步骤1中查到的目录)
USE master
GO-- 将下面的文件目录修改为你自己的
CREATE DATABASE _sys_resource
ON(
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_mssqlsystemresource.mdf'
)
LOG ON(
FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\_mssqlsystemresource.ldf'
)
FOR ATTACH
GOUSE _sys_resource
GODROP PROC sys.xp_cmdshell
GOEXEC sp_detach_db N'_sys_resource'
GO-- 要重启 sql 才生效,下面这句让 sql 停止
SHUTDOWN
GO
-- 4. 回到步骤 2 的目录,将下面两个文件改名以做备份
mssqlsystemresource.mdf
mssqlsystemresource.ldf-- 5. 将下面两个文件改名,去掉前面的 _
_mssqlsystemresource.mdf
_mssqlsystemresource.ldf
-- 6. 重新启动 sql server, 再执行 xp_cmdshell, 你会发现找不到这个存储过程了
Database '_sys_resource' already exists. Choose a different database name.
Msg 3701, Level 11, State 5, Line 2
Cannot drop the procedure 'sys.xp_cmdshell', because it does not exist or you do not have permission.
Msg 3703, Level 16, State 3, Line 1
Cannot detach the database '_sys_resource' because it is currently in use.郁闷。
拒绝权限就好了..
加入sqlserver2005可以禁止掉xp_cmdshell..
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
Configuration option 'xp_cmdshell' changed from 0 to 0. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
最后,还是能执行CMD命令~
你重启一下服务看看.
Database Engine->xp_cmdshell->取消选中->确定
重启SQLServer服务.