Create Application Roles using TSQL

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 9 Jun 2020

Description

Create application roles using TSQL.

With examples of assigning rights to the roles

Code

USE myDBGO--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- -- 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_RoleGOIF EXISTS (SELECT 1 FROM sysusers WHERE name = 'TEST_Reader_Role' AND IsAppRole = 1)DROP APPLICATION ROLE TEST_Reader_RoleGO--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- -- create the new Application roles -- --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- CREATE APPLICATION ROLE TEST_Admin_RoleWITH PASSWORD = 'Th3Spy!nth3Gre3nH4t', DEFAULT_SCHEMA = dbo;GOCREATE APPLICATION ROLE TEST_Reader_RoleWITH PASSWORD = 'H0w2St34lth3W0rld', DEFAULT_SCHEMA = dbo;GO--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- -- grant the new admin role God-like priveleges -- --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-- GRANT DELETE ON SCHEMA::[dbo] TO [TEST_Admin_Role]GOGRANT EXECUTE ON SCHEMA::[dbo] TO [TEST_Admin_Role]GOGRANT INSERT ON SCHEMA::[dbo] TO [TEST_Admin_Role]GOGRANT SELECT ON SCHEMA::[dbo] TO [TEST_Admin_Role]GOGRANT UPDATE ON SCHEMA::[dbo] TO [TEST_Admin_Role]GOGRANT 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]GOGRANT SELECT ON SCHEMA::[dbo] TO [TEST_Reader_Role]GOGRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [TEST_Reader_Role]GO