Satheesh Kannan Chandra's Blog

SQL Server has two types of roles:

standard roles and
applicaton roles.

Standard roles function like groups. For standard roles, users are assigned to the roles and then permissions are granted to those roles. Application roles are different from other roles; they never have member. The main purpose of the application role is to provide an extra layer to SQL Server security mechanism.

First the application (Query Analyser, VB application or a web application) connects to the database using a username and password. This may be the guest account or a trusted Windows NT account. Then the application switches to the application role using the sp_setapprole system stored procedure. Once this command is issued on a SQL Server connection all security is reset to whatever permissions have been granted to the particular application role. This command overrides any per user permissions assigned. The only way to disable this and revert back to your user permissions is to close the connection and reopen it.


To understand the ability of the application role, we will play like this in query analyser.
Assume query analyser is our sample application.


Open a session with your SQL server thru query analyser. and execute the following T-SQL commands.

use pubs

select * from sales
-- Having access to sales table as a 'sa' user.

sp_addapprole 'testapp','apppass'
-- Adding application role 'testapp' with password 'testapp'

deny SELECT on [sales] to [testapp]
-- Denying SELECT permisson to testapp application role.

sp_setapprole 'testapp','apppass'
-- Now the application (Query Analyser) switches
-- its security context from 'sa' to 'testapp' application role.
-- SQL Server returns the following message
-- "The application role 'testapp' is now active."

select * from sales
-- The current connection wont be able to access pubs.dbo.sales table
-- since, it is in application role's context.. even the connection
-- user is 'sa'.
-- SQL Server throws the following error message.

/*
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'sales', database 'pubs', owner 'dbo'.
*/


An important note is that application roles are database specific. Also it is connection specific. In out previous example, if you open an another connection to the SQL server thru 'sa'. Then the new connection will not have any impact from 'application role'.