Database Reader Role with the ability to Create, View and execute stored procedure's and view's in sql server

Question

I will need to allow report writers on the Subscription database to be able to view the table, create alter and delete stored procedure and views. Since it is a transactional replication they cannot alter or delete table structure. Is this possible if so how to achieve any ideas ?

Answer 1

Create an explicit schema or schemas that are separate from any other schemas and objects that are replicated from your publishers.

GRANT ALTER ON SCHEMA::YourNewSchema TO User_Who_Can_Create_Stored_Procs;
GRANT CREATE PROC TO User_Who_Can_Create_Stored_Procs;
GRANT ALTER PROC TO User_Who_Can_Create_Stored_Procs;
GRANT DROP PROC TO User_Who_Can_Create_Stored_Procs;