Create a SQL Server Login and User with limited permissions for CRUD application Access
If you want a user to be able to read any table and view in your database then you would run the following from SQL Server 2012 onwards

ALTER ROLE db_datareader ADD MEMBER MyUser;


For previous versions you would run

sp_addrolemember 'db_datareader','MyUser';


db_datareader is a built in role that grants read access to everything.
db_datawriter is its equivalent for writes.

If you want to grant limited CRUD access then you need to create a new role and use the GRANT statement to assign permissions to that role.  After you have done that you can use the statements above to make your user a member of your new role as well.

CREATE ROLE MyLimitedCRUDRole;


For the stored procedure part of your question I would set up an explicit role for stored proc access.  I would keep this separate to MyLimitedCRUDRole as the visibility of what MyLimitedCRUDRole is of increasingly high importance in a GDPR world.

I would also advise having roles that have clear and single purpose for clarity.

CREATE ROLE db_AllProcExecutor;
GRANT EXECUTE ON MyStoredProc to db_AllProcExecutor;


It is possible to generate the GRANT statements dynamically but on any database with a security sensitivity I would be very careful doing so.