SQL2005禁用相关存储过程方法

在SQL SERVER 2000中，可以通过　exec master..sp_dropextendedproc 方法删除系统扩展存储过程。然而，到2005后，因为有些系统扩展存储过程系统也要使用，因此，就不能删除了，可以采用

deny execute on [系统扩展存储过程名] to [角色]

的方法，将相关的系统存储过程禁用。

以下是网上流传的一些“危险”的存储过程：

deny execute on xp_cmdshell to public
deny execute on xp_dirtree to public
deny execute on xp_fileexist to public
deny execute on xp_getnetname to public
deny execute on sp_oamethod to public 
deny execute on sp_oacreate to public 
deny execute on xp_regaddmultistring to public 
deny execute on xp_regdeletekey to public 
deny execute on xp_regdeletevalue to public 
deny execute on xp_regenumkeys to public 
deny execute on xp_regenumvalues to public
deny execute on xp_regread to public 
deny execute on xp_regwrite to public 
deny execute on xp_readwebtask to public 
deny execute on xp_makewebtask to public 
deny execute on xp_regremovemultistring to public 
deny execute on sp_OACreate to public
deny execute on sp_addextendedproc to public

然后，我们可以通过下列的方法，查看系统扩展存储过程的禁用情况：

select dp.NAME AS principal_name,
       dp.type_desc AS principal_type_desc,
       o.NAME AS object_name,
       p.permission_name,
       p.state_desc AS permission_state_desc 
from    sys.database_permissions p
left    OUTER JOIN sys.all_objects o
on     p.major_id = o.OBJECT_ID
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
and p.grantee_principal_id=DATABASE_PRINCIPAL_ID('public')