Grant rights to a role

if exists (Select Name from SysObjects Where Name='GrantRightstoRole')
drop procedure GrantRightstoRole
go

Create procedure GrantRightstoRole(@User varchar(20))
AS 
Begin
Declare @ALLTabs varchar(5000)
          Declare @ALLSps  varchar(5000)
Declare @Sql varchar(1000)

          Declare UserTables cursor For select name  from sysobjects where type='u'
          Declare UserSps cursor For Select Name from Sysobjects  where type='p'
--Granting For Tables
Open UserTables
Fetch Next from Usertables into @alltabs
while @@Fetch_status=0
Begin 
Set @Sql ='GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON   '  + @ALLTABS +  '   TO  ' + @User + ''
Execute (@sql)
Fetch Next from Usertables into @alltabs

end
Close usertables
Deallocate usertables

Open UserSps
Fetch Next from UserSps into @ALLSPS
while @@Fetch_status=0
Begin 
Set @Sql ='GRANT  Execute  ON    ' + @ALLSPS + '   TO  ' + @User + ' '
execute (@sql)
Fetch Next from UserSps into @ALLSPS
End
Close UserSps
Deallocate UserSps
End
go