I have just moved a SQL Server 2000 DB over to SQL Server 2005 Express (which will be upgraded soon) and in the process I am cleaning up some bad habits from the previous owner.
For one, the old web app used the "sa" account to access the DB. I've now created a new Login and mapped that to a User in this specific database with db_datawriter and db_datareader roles. Login works fine... but when the app attempts to execute any stored proc, I get an error about the proc not being found. It's a permissions issue... Connecting to the server via Management Studio using the new credentials shows me that none of the app's stored procs are even visible, so the error makes sense.
But... there are two or three hundred stored procs here. How do I grant this user access to execute any of the stored procs without having to modify permissions on every one? For now I went and added the "db_owner" role for this user. But that seems like overkill... ?