SQLServer 安全加固：ICP备案需要禁用的扩展存储过程

当创建一个任意类型的登录账号是，sql server 默认会授予public服务器角色，或者数据库角色，并且不能撤销该角色。而public权限非常多，大部分是查询元数据的权限，其他还可以读取操作系统的各种信息！所以sql server 一个普通的账号，都可以获取所有数据库和操作系统信息了！

当前主要是禁用几个扩展存储过程的权限。

sp_OACreate  
sp_OADestroy  
sp_OAGetErrorInfo  
sp_OAGetProperty  
sp_OAMethod  
sp_OASetProperty  
sp_OAStop  
sp_regaddmultistring  
xp_regdeletekey  
xp_regdeletevalue  
xp_regenumvalues   
xp_regremovemultistring 
xp_cmdshell  
xp_perfend   
xp_perfmonitor  
xp_perfsample   
xp_perfstart  
xp_readerrorlog  
xp_readmail  
xp_revokelogin   
xp_runwebtask 
xp_schedulersignal  
xp_sendmail  
xp_servicecontrol   
xp_snmp_getstate  
xp_snmp_raisetrap   
xp_sprintf  
xp_sqlinventory   
xp_sqlregister 

use master  
go  
SELECT A.name,schema_name(A.schema_id) [schema],A.type,b.permission_name,B.type,B.state_desc,C.name,c.type_desc  
FROM sys.all_objects AS A    
LEFT JOIN sys.database_permissions AS B ON B.major_id=A.object_id AND B.minor_id=0 AND B.class=1    
LEFT JOIN sys.database_principals AS C ON C.principal_id = B.grantee_principal_id    
where A.name IN('sp_OACreate','sp_OADestroy','sp_OAGetErrorInfo','sp_OAGetProperty','sp_OAMethod','sp_OASetProperty'
,'sp_OAStop','sp_regaddmultistring','xp_regdeletekey','xp_regdeletevalue','xp_regenumvalues','xp_regremovemultistring'
,'xp_cmdshell','xp_perfend','xp_perfmonitor','xp_perfsample','xp_perfstart','xp_readerrorlog','xp_readmail'
,'xp_revokelogin','xp_runwebtask','xp_schedulersignal','xp_sendmail','xp_servicecontrol','xp_snmp_getstate'
,'xp_snmp_raisetrap','xp_sprintf','xp_sqlinventory','xp_sqlregister') 
ORDER BY A.name

--生成拒绝(deny)权限的脚本
use master  
go  
SELECT A.name,schema_name(A.schema_id) [schema],A.type,b.permission_name,B.type,B.state_desc,C.name,c.type_desc  
,'DENY EXECUTE ON '+A.name+' TO public'
FROM sys.all_objects AS A    
LEFT JOIN sys.database_permissions AS B ON B.major_id=A.object_id AND B.minor_id=0 AND B.class=1    
LEFT JOIN sys.database_principals AS C ON C.principal_id = B.grantee_principal_id    
where A.name IN('sp_OACreate','sp_OADestroy','sp_OAGetErrorInfo','sp_OAGetProperty','sp_OAMethod','sp_OASetProperty'
,'sp_OAStop','sp_regaddmultistring','xp_regdeletekey','xp_regdeletevalue','xp_regenumvalues','xp_regremovemultistring'
,'xp_cmdshell','xp_perfend','xp_perfmonitor','xp_perfsample','xp_perfstart','xp_readerrorlog','xp_readmail'
,'xp_revokelogin','xp_runwebtask','xp_schedulersignal','xp_sendmail','xp_servicecontrol','xp_snmp_getstate'
,'xp_snmp_raisetrap','xp_sprintf','xp_sqlinventory','xp_sqlregister') 
ORDER BY A.name

 网上建议删除的几个扩展存储过程,如public有权限,可用拒绝.

--网上建议删除的几个扩展存储过程,如public有权限,可用拒绝.
SELECT A.name,schema_name(A.schema_id) [schema],A.type,b.permission_name,B.type,B.state_desc,C.name  
,'DENY '+b.permission_name COLLATE Chinese_PRC_CI_AS +' ON '+A.name+' TO '+C.name  
FROM master.sys.all_objects AS A    
LEFT JOIN master.sys.database_permissions AS B ON B.major_id=A.object_id AND B.minor_id=0 AND B.class=1    
LEFT JOIN master.sys.database_principals AS C ON C.principal_id = B.grantee_principal_id    
where A.name IN('sp_makewebtask','xp_cmdshell','xp_dirtree','xp_fileexist','sp_oamethod','sp_oacreate'
,'xp_regaddmultistring','xp_regdeletekey','xp_regdeletevalue','xp_regenumkeys','xp_regenumvalues'
,'sp_add_job','sp_addtask','xp_regread','xp_regwrite','xp_readwebtask','xp_makewebtask','xp_regremovemultistring')  
ORDER BY A.name    
GO