USE myDB GO --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- -- drop the existing Application roles if they exist -- --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- IF EXISTS (SELECT 1 FROM sysusers WHERE name = 'TEST_Admin_Role' AND IsAppRole = 1) DROP APPLICATION ROLE TEST_Admin_Role GO IF EXISTS (SELECT 1 FROM sysusers WHERE name = 'TEST_Reader_Role' AND IsAppRole = 1) DROP APPLICATION ROLE TEST_Reader_Role GO --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- -- create the new Application roles -- --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- CREATE APPLICATION ROLE TEST_Admin_Role WITH PASSWORD = 'Th3Spy!nth3Gre3nH4t' , DEFAULT_SCHEMA = dbo; GO CREATE APPLICATION ROLE TEST_Reader_Role WITH PASSWORD = 'H0w2St34lth3W0rld' , DEFAULT_SCHEMA = dbo; GO --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- -- grant the new admin role God-like priveleges -- --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- GRANT DELETE ON SCHEMA::[dbo] TO [TEST_Admin_Role] GO GRANT EXECUTE ON SCHEMA::[dbo] TO [TEST_Admin_Role] GO GRANT INSERT ON SCHEMA::[dbo] TO [TEST_Admin_Role] GO GRANT SELECT ON SCHEMA::[dbo] TO [TEST_Admin_Role] GO GRANT UPDATE ON SCHEMA::[dbo] TO [TEST_Admin_Role] GO GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [TEST_Admin_Role] GO --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- -- grant the new reader role Developer-like priveleges -- --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- GRANT EXECUTE ON SCHEMA::[dbo] TO [TEST_Reader_Role] GO GRANT SELECT ON SCHEMA::[dbo] TO [TEST_Reader_Role] GO GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [TEST_Reader_Role] GO