This sql generates a grant blanket execute on stored procedures in a database:
GRANT EXECUTE TO {USER_NAME}
GO
This sql generates a grant execute statement for each stored procedure in the database:
SELECT ‘GRANT EXECUTE ON ‘ + name + ‘ TO {USER_NAME}’
FROM sysobjects
WHERE type IN ( ‘P’, ‘FN’ )
AND name NOT LIKE ‘dt_%’
ORDER BY name
This scripts grants execute to all stored procedures in the database:
USE {DATABASE_NAME}
GO
DECLARE @proc_name varchar(128)
DECLARE @sql nvarchar(255)
DECLARE proc_cur CURSOR FAST_FORWARD
FOR
SELECT name
FROM sysobjects WITH (NOLOCK)
WHERE type IN (‘P’,’FN’)
OPEN proc_cur
FETCH proc_cur INTO @proc_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘grant execute on ‘ + @proc_name + ‘ to {USER_NAME}’
EXECUTE sp_executesql @sql
FETCH proc_cur INTO @proc_name
END
CLOSE proc_cur
DEALLOCATE proc_cur
GO