1)How to Create User Define Role?
2)How to Give Particular Access to Role?
1) Create Read_Only Role And Give the Access of Viewing any Definition.
Create role Read_Only
GRANT VIEW ANY DEFINITION TO Read_Only
Revoke VIEW ANY DEFINITION TO Read_Only
2) Create Role and Give Execute Role
Create role DB_Execute
Grant Execute to DB_Execute
Revoke Execute to DB_Execute
3) Create Role And Give Alter Role
Create role DB_Alter
Grant Alter to DB_Alter
Revoke Execute to DB_Alter
4) Create Role And Give Insert,Update,Delete Role
Create Role DB_Write_Database
Grant Insert,Update,Delete to DB_Write_Database
Revoke Insert,Update,Delete to DB_Write_Database
5)If you want to Create Read-Only Definition to All DB_User expect Admin Role.
GRANT VIEW ANY DEFINITION TO PUBLIC
Revoke View Any Definition to Public