We had a horribly pernicious timeout problem recently. It was occurring in a .Net 2.0 application written to retrieve  reports from SQL Server 2005 Reporting Services via its web services interface. The query behind the report was a bit large, but typically would run under 30 seconds. We had judged that new indexes were out of the question because of their impact on the already slow program that loads the key data. All of the sudden, we started getting continuous timeouts. After a couple of days, the underlying query was up to 15 minutes execution time!

In the end multiple interacting problems had to be solved in order to get the reports running properly. For the past few weeks they've been running without a hitch.

Problem: You've transferred or run a bunch of stored procedure scripts, but you can't execute them. Reason - execute permission denied. You forgot to put a grant statement in your script.

Solution: The trivial solution is, of course, GRANT EXECUTE ON {your proc name} TO PUBLIC. Slightly less trivial is to grant to a specific role, but most people needing this tip will only be using PUBLIC.

Wouldn't it be great to automate this for all stored procedures in the database? Well, here you go: