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