在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')