Application Roles – SQL Server

An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application.

In SQL Server, application roles cannot access server-level metadata because they are not associated with a server-level principal.

Application Role

The following steps make up the process by which an application role switches security contexts:

  • A user executes a client application.
  • The client application connects to an instance of SQL Server as the user.
  • The application then executes the sp_setapprole stored procedure with a password known only to the application.
  • If the application role name and password are valid, the application role is enabled.
  • At this point the connection loses the permissions of the user and assumes the permissions of the application role.

Application related tasks

TaskType
CreateCREATE APPLICATION ROLE
AlterALTER APPLICATION ROLE
DeleteDROP APPLICATION ROLE
SETsp_setapprole

CREATE APPLICATION ROLE

Adds an application role to the current database.

Syntax:

CREATE APPLICATION ROLE application_role_name   
    WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ] 

Example:
The following example creates an application role called weekly_receipts that has the password 987Gbv876sPYY5m23 and Sales as its default schema.

CREATE APPLICATION ROLE weekly_receipts   
    WITH PASSWORD = '987G^bv876sPY)Y5m23'   
    , DEFAULT_SCHEMA = Sales;  
GO

ALTER APPLICATION ROLE

Changes the name, password, or default schema of an application role.

Syntax:

ALTER APPLICATION ROLE application_role_name
    WITH <set_item> [ ,...n ]  
<set_item> ::=
    NAME = new_application_role_name
    | PASSWORD = 'password'  
    | DEFAULT_SCHEMA = schema_name

Example:
Changing the name, password, and default schema. The following example changes the name, password, and default schema of the application role receipts_ledger all at the same time.

ALTER APPLICATION ROLE receipts_ledger   
    WITH NAME = weekly_ledger,   
    PASSWORD = '897yUUbv77bsrEE00nk2i',   
    DEFAULT_SCHEMA = Production;  
GO

DROP APPLICATION ROLE

Removes an application role from the current database.

Syntax:

DROP APPLICATION ROLE rolename

Example:
Drop application role “weekly_ledger” from the database.

DROP APPLICATION ROLE weekly_ledger;  
GO

sp_setapprole

Activates the permissions associated with an application role in the current database.

Syntax:

sp_setapprole [ @rolename = ] 'role',  
    [ @password = ] { encrypt N'password' }
      |  
        'password' [ , [ @encrypt = ] { 'none' | 'odbc' } ]  
        [ , [ @fCreateCookie = ] true | false ]  
    [ , [ @cookie = ] @cookie OUTPUT ]

Example:
Activating an application role without the encrypt option. The following example activates an application role named SalesAppRole, with the plain-text password AsDeF00MbXX, created with permissions specifically designed for the application used by the current user.

EXEC sys.sp_setapprole 'SalesApprole', 'AsDeF00MbXX';  
GO

 

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply