/*
sp_GrantAllProcs
Written By: John Fichera jfichera@nc.rr.com
Date: 10.2.01
Overview: Grants execute permissions to all stored procs except system stored procs against database.
Pass it username or role in single quotes.  Install in master to run from all databases.

*/


if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_grantallprocs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_grantallprocs]
GO

create procedure sp_grantallprocs @username varchar (100)
as 

declare @sql varchar (1000)
set nocount on
--check for existing user or role in sysusers
if @username in (select name from sysusers where uid between 16384 and 16393)
	begin
	Print 'Cannot Grant permissions to System Roles'
	Return 
	end

if @username is null or @username not in (select name from sysusers)
	begin
	print 'Username does not exist in sysusers or none specified.'
	return
	end


declare @objectname varchar(50),@count int,@x int,@ownername varchar (50),
@error int

create table #ObjSuccess (objName varchar (100),objOwner varchar (100))
create table #ObjFailure (objName varchar (100),objOwner varchar (100))

select @error = null
select @sql=null

--Grant execute to all stored procedures
declare cur cursor for
--Get the name of all stored procs
select s.name, o.name from sysobjects o inner join sysusers s on s.uid = o.uid 
where  o.uid <> 3 and o.type = 'P' and o.name not like 'dt%'

--I do this cause I don't wanna use @@fetch status
select @count = count(id) from sysobjects where type='P' and name not like 'dt%'
select @x=0
open cur

while @count <> @x
	begin
	fetch next from cur into @ownername,@objectname
	select @sql='GRANT EXECUTE ON [' + @ownername+'].['+@objectname + '] TO [' + @username + ']'
	execute (@sql)
	select @error = @@error
	--Error check
	if @error = 0
		begin
		select 'EXECUTE granted on '+@ownername+'.'+@objectname+' to User or Role '+@username
		insert into #objSuccess values (@objectname,@ownername)
		end
	else 	
		begin
		select 'Problem granting permissions on '+@ownername+'.'+@objectname+' Error number '+ cast (@error as varchar (5))
		insert into #objFailure values (@objectname,@ownername)
		end
	select @x=@x+1
	end


close cur
deallocate cur
select @error = null



--Report
if exists (select * from #objSuccess)
	begin
	print 'Successfully applied permissions to the following objects'
	select objName as "Object Name" ,objOwner as "Owned By"  from #objSuccess order by 1
	end
if exists (select * from #objFailure)
	begin
	print 'Failed to apply permissions to the following objects'	
	select objName as "Object Name" ,objOwner as "Owned By"  from #objSuccess order by 1
	end