Managing Application Roles : Role « User Role « SQL Server / T-SQL Tutorial






  1. SQL Server / T-SQL Tutorial
  2. User Role
  3. Role
CREATE APPLICATION ROLE DataWareHouseApp
WITH PASSWORD = 'YourPass',
DEFAULT_SCHEMA = dbo

-- Now grant this application role permissions
GRANT SELECT ON YourTable1
TO DataWareHouseApp

EXEC sp_setapprole 'DataWareHouseApp', -- App role name
'YourPass' -- Password

-- Works
SELECT COUNT(*) FROM YourTable1

ALTER APPLICATION ROLE DataWareHouseApp
WITH NAME = DW_App, PASSWORD = 'YourPass2'

DROP APPLICATION ROLE DW_App








28.9.Role
28.9.1.Some of the SQL Server fixed server roles
28.9.2.Managing Server Role Members
28.9.3.Reporting Fixed Server Role Information
28.9.4.Managing User-Defined Database Roles: grant
28.9.5.Managing User-Defined Database Roles: alter role
28.9.6.Managing User-Defined Database Roles: drop role
28.9.7.Managing Application Roles
28.9.8.Managing Fixed Database Role Membership: sp_droprolemember
28.9.9.Managing User-Defined Database Roles: Create
28.9.10.Reporting Fixed Database Roles Information
28.9.11.Managing Fixed Database Role Membership: sp_addrolemember