Allow User to use all tables views and stored procedures
If you need to allow a role or user to access all tables future and present as well as stored procedures. All you need to do is add them to the db_datareader and db_datawriter roles. (This allows them access to all tables and views) and then GRANT EXECUTE to give access to stored procedures).
Below creates a role with the correct permissions we want. You would then just add your Windows groups or SQL accounts to it.
CREATE ROLE [App_execute] AUTHORIZATION [dbo] GO ALTER ROLE [db_datareader] ADD MEMBER [App_execute] GO ALTER ROLE [db_datawriter] ADD MEMBER [App_execute] GO GRANT EXECUTE TO [App_execute] GO
Add a member to the role via:
ALTER ROLE [App_execute] ADD MEMBER [DOMAIN\AppUserGroup] GO