SQL Fool Adventures in SQL Tuning – a blog for the rest of us

DECLARE @schema_owner VARCHAR(20);
SET @schema_owner = 'dbo';
 
SELECT [name] AS 'storedProcedure', 'Grant Execute On '
    + @schema_owner + '.' + [name]
    + ' To [insertDatabaseRoleHere];' AS 'sqlCode'
FROM sys.objects WITH (NoLock)
WHERE [name] Not In (
    SELECT o.name
    FROM sys.database_permissions p WITH (NoLock)
    INNER Join sys.objects o WITH (NoLock)
        ON p.major_id = o.OBJECT_ID
    INNER Join sys.database_principals u WITH (NoLock)
        ON u.principal_id = p.grantee_principal_id
    WHERE u.name = '[insertDatabaseRoleHere]'
)
And [type] = 'P';