IT狂想曲~

批次取得資料庫中全部的 Stored Procedure、View、Function

select
	[id] , [sp_name] , [u_name] , [xtype] , [type_desc] ,
	'GRANT ' + [GCMD] + ' ON ['+ [u_name] +'].[' + sp_name + '] TO [SomeUser]' [GRANT_CMD] ,
	'REVOKE ALL ON ['+ [u_name] +'].[' + sp_name + '] TO [SomeUser]' [REVOKE_CMD]
from (
    select
		o.id , o.name [sp_name] , u.name [u_name] , o.xtype ,
		case 
			when o.xtype in ('P','FN', 'IF', 'TF') then 'EXECUTE'
			when o.xtype in ('V') then 'SELECT'
		end [GCMD] ,
		case 
			when o.xtype = 'P' then 'Stored Procedure'
			when o.xtype in ('FN', 'IF', 'TF') then 'Function'
			when o.xtype in ('V') then 'View'
		end [type_desc] 
    from sysobjects o
    join sysusers u on o.uid = u.uid    
    where o.xtype in ('P', 'FN', 'IF', 'TF' , 'V' ) and o.category = 0
) t