Grant SPROC Permission to an Application Account

With the principle of least privilege of application / database account, web app normally uses a DB account with limited permission to specific database objects in Connection String.

Manually typing of "GRANT EXECUTE ON <Object> to <User>" seem handy but it's hard to manage if the number of SPROCs, Functions or Views is over tens or hundreds.

This article shows how can we grant permission in batch easily: http://www.codeproject.com/KB/database/T-SQL.aspx

The key point is to retrieve the name of all custom objects from sysobjects table, for example,

declare cur_myobj cursor for select name from sysobjects where type='P' and name like 'MyApp_%'