database administration - SQL Server - Execute Stored Procedure Only Role -

how create custom sql server database server role can run select queries , stored procedures?

meaning, users of role won't allowed custom queries, can run stored procedures has crud , sysadmin statements -- updates, deletes, alters, drops.

i tried creating custom role, failed when ran sp alters table.

create role supportstaff grant select supportstaff grant execute supportstaff 

any ideas?

update

okay, found above code allows stored procedures insert/update/delete statements. doesn't allow alter, truncate or drop index statements.

for alter, need add grant alter supportstaff

but need allow truncate , drop index?

create role , make member of db_datareader add execute permission each procedure individually. example user called test , member of role. run admin:

create table test (id int) go  create procedure inserttest begin insert dbo.test         (id) values         (1) end go    grant execute on dbo.inserttest test go 

if procs inset data tables, , don't break object's ownership chain, should fine set up. try user:

select * dbo.test --sucess insert dbo.test(id)values(1) -- fail exec inserttest  --sucess