SQL Server - Execute Stored Procedure Only Role

Question

How do I create a custom SQL Server database server role that can only run SELECT queries and stored procedures?

Meaning, users of this role won't be allowed to do custom queries, but can run stored procedures that has CRUD and SysAdmin statements -- UPDATES, DELETES, ALTERS, DROPS.

I tried creating this custom role, but failed when I ran an SP that alters a table.

CREATE ROLE SupportStaff
GRANT SELECT TO SupportStaff
GRANT EXECUTE TO SupportStaff

Any ideas?

Update

Okay, so I found that the above code allows Stored Procedures with INSERT/UPDATE/DELETE statements. But it doesn't allow ALTER, TRUNCATE or DROP INDEX statements.

For ALTER, I simply need to add GRANT ALTER TO SupportStaff

But what do I need to do to allow TRUNCATE and DROP INDEX?

Answer 1

create a role and make it member of db_datareader then add EXECUTE permission to each procedure individually. Example with an user called test and member of that role. Run this as an admin:

CREATE TABLE test (id INT)
GO

CREATE PROCEDURE INSERTtest
AS
begin
INSERT INTO dbo.test
        (id)
VALUES
        (1)
END
GO  

GRANT EXECUTE ON dbo.INSERTtest TO test
GO

If your procs inset data into the tables, and they don't break the object's ownership chain, you should be fine with this set up. Try this with the user:

SELECT * FROM dbo.test --sucess
INSERT INTO dbo.test(id)VALUES(1) -- fail
EXEC INSERTtest  --sucess