Create Procedure Up_Grant_All
(@Objecttypes varchar (3)
)
AS
Declare @da_Objectname varchar (30)
Declare @da_ObjectType Varchar (30)
Declare @ls_sql varchar (255)
/ *
** IF We're In a Transaction, Disallow this Since It Might Make Recovery
** impossible.
* /
IF @@ TRANCOUNT> 0
Begin
/ * 17260, "Can't Run% 1! From within a transaction." * /
Raiserror 17260, "UP_GRANT_ALL"
Return (1)
end
IF Upper (@ObjectTypes) = 'all'
Begin
DECLARE CUR_RESULT2 CURSOR for
Select Name, Type
From sysobjects
end
Else
Begin
DECLARE CUR_RESULT2 CURSOR for
Select Name, Type
From sysobjects
Where sysobjects.type = Upper (@ObjectTypes)
end
Open cur_Result2
FETCH CUR_RESULT2 INTO @da_Objectname, @ da_ObjectType
While (@@ sqlstatus = 0)
Begin
IF @da_objecttype = 'u' or @da_objecttype = 'v'
Begin
EXEC ('Grant All on' @da_objectname 'to public')
end
Else if @da_ObjectType = 'P'
Begin
EXEC ('Grant Execute ON' @ da_Objectname 'to public')
end
FETCH CUR_RESULT2 INTO @da_Objectname, @ da_ObjectType
end
Close Cur_Result2
DEAALLOCATE CURSOR CUR_RESULT2
