Grant Execute Permissions For All Stored Procedures To A User

You want to add a new user with read and write access and also the ability to execute all stored procedures but you don't want to make the user a db_owner.
The code below will do a GRANT EXECUTE for all the procedures in the DB
This line will skip those dt_ procedures that are in every database
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0

Right now this code prints the GRANT EXECUTE statements, change the PRINT to EXEC if you want it to be done automatically

--Grab all the procedures for the current DB
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME
INTO #Procedurelist
FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY SPECIFIC_NAME

DECLARE
@Loopid INT,
@MaxId INT,
@UserName VARCHAR(50)


--This is the user that will get the execute permissions
SELECT @UserName = 'SomeUser'


--Grab start and end values for the loop
SELECT @Loopid = 1,
@MaxId = MAX(ID)
FROM #Procedurelist

DECLARE
@SQL VARCHAR(500),
@ProcName VARCHAR(400)


--This is where the loop starts
WHILE @Loopid <= @MaxId BEGIN

--grab the procedure name
SELECT @ProcName = SPECIFIC_NAME
FROM #Procedurelist
WHERE ID = @Loopid

--construct the statement
SELECT @SQL = 'GRANT EXECUTE ON ' + @ProcName + ' TO ' + @UserName
PRINT (@SQL) --change PRINT to EXECUTE if you want it to run automatically

--increment counter
SET @Loopid = @Loopid + 1
END

--clean up
DROP TABLE #Procedurelist