Cool Tips‎ > ‎SQL Server Security‎ > ‎

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 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





ċ
Andy Hughes,
Jun 9, 2020, 5:49 PM
Comments