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';