SQL Server Database Role not working I have tried to set up a database role that the users are able to execute or view stored procedures or functions as well as SELECT, INSERT, UPDATE, DELETE data on tables via stored procs. This is what I have done is;

CREATE ROLE  db_execute_procedure_only
GRANT EXECUTE ON SCHEMA::dbo TO db_execute_procedure_only
GRANT  VIEW DEFINITION ON SCHEMA::dbo TO db_execute_procedure_only
DENY CONTROL  ON SCHEMA::dbo TO db_execute_procedure_only


Then set the users database membership to db_execute_procedure_only

Problem is it does not work in SSMS. I have tracked down the problem. If I explicitly state execute as user = 'XXXXXX', I get the desired result. Issue is that SSMS uses CURRENT_USER which still points to dbo which has db_owner rights. 

Question is - how can I get CURRENT_USER pointing to a user which has say read/write only access?
CURRENT_USER refers to the user role your current login has on the specific database.
For instance i have a user called PlebUser, who has db_owner rights on a specific database A, has no rights (not even viewing) on database B and has generic guest rights (server role public) for my master db (C).
Running Select CURRENT_USER for each of these databases returns me:

A: PlebUser (defined role on my database)
B: Nothing, can't even run it because i'm denied on this DB
C: Guest

You need to check whether you are logged in as your defined user (and not as DBO/SA, whatever admin account you are using to create the new users), and that they are properly mapped to specific databases.
You can do this by checking the server wide security logins, properties on the specific login and check user mapping.