Eric M Russell (10/5/2015)
Ideally, each application or user account should be a member of a role, and the only additional permissions for each role should be execution on only the procedures that role needs. So they can see the procedures (but not the T-SQL), execute the procedures, but can't read or modify anything outside the context of the procedure.
I agree that is a good methodology. I think it can be further improved by standardizing an external interface to the stored procedures. Part of that interface would include the logged-in user. Then, from the application developer's perspective they would simply write:
Database.Load(userID, object)
Database.Store(userID, object)
Database.Delete(userID, object)
Total security while dramatically simplifying the application.
Note: the userID might be null, depending on the application. So, it supports role-based or user-based security on either application-side or database-side, or both.