MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1203

Good tips, but when you grant the permission to windows authentication users, need to add square branket for the user when pass the parameter. also the sp will need the following change:

SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO [' + @user +']'

 The stored procedure as written will only grant privileges to stored procedures and not stored functions.  To grant to both types change section 3's insert from:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
    SELECT ROUTINE_SCHEMA, ROUTINE_NAME
      FROM INFORMATION_SCHEMA.ROUTINES
     WHERE ROUTINE_NAME NOT LIKE 'dt_%'
       AND ROUTINE_TYPE = 'PROCEDURE'

 to:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
    SELECT ROUTINE_SCHEMA, ROUTINE_NAME
      FROM INFORMATION_SCHEMA.ROUTINES
     WHERE ROUTINE_NAME NOT LIKE 'dt_%'
       AND DATA_TYPE <> N'TABLE'
       AND (   ROUTINE_TYPE = N'PROCEDURE'
            OR ROUTINE_TYPE = N'FUNCTION')

VERY easy for SQL Server 2005. 

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Troy