Create Application Roles using TSQL

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