Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.
EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO
EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO
SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
INTO #NewPermissions
FROM sysobjects
WHERE xtype = 'P'
AND ( name LIKE 'sp[_]%' OR
name LIKE 'up[_]%')
ORDER BY name
GO
DECLARE @SQLStatement VARCHAR(512)
DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
FROM #NewPermissions
OPEN SPPermissions
FETCH NEXT
FROM SPPermissions
INTO @SQLStatement
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing: ' + @SQLStatement
EXEC (@SQLStatement)
FETCH NEXT
FROM SPPermissions
INTO @SQLStatement
END
CLOSE SPPermissions
DEALLOCATE SPPermissions
GO