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?