While attempting to schedule a SSRS 2005 report by creating a SUBSCRIPTION, I encountered the error Execute permission denied on object ‘sp_add_category’.
Followed the following steps recommended on TechNet:
1. Open Start –> All Programs –> Microsoft SQL Server 2005–> Configuration Tools–>Reporting Services Configuration2. Select the instance (local or remote)3. Click on Database Setup4. On the Credentials Type, select a different type than a current one. I selected Windows Credentials and enter the Account Name and Password for the account with appropriate priveleges.5. Press Apply – This will grant database privileges.6. Try creating a subscription.
This worked for me.
SylviaVargas's Blog 