1) Create Read_Only Role And Give the Access of Viewing any Definition. USE master GO Create role Read_Only GRANT VIEW ANY DEFINITION TO Read_Only USE master GO Revoke VIEW ANY DEFINITION TO Read_Only2) Create Role and Give Execute Role Create role DB_Execute Grant Execute to DB_Execute Revoke Execute to DB_Execute3) Create Role And Give Alter Role Create role DB_Alter Grant Alter to DB_Alter Revoke Execute to DB_Alter4) 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_Database5)If you want to Create Read-Only Definition to All DB_User expect Admin Role. USE master GO GRANT VIEW ANY DEFINITION TO PUBLIC USE master GO Revoke View Any Definition to Public
Tags: Create User Define Role and Give Access