Microsoft® SQL Server 2012 Unleashed

Application Roles

Unlike other roles, application roles contain no database users. When an application role is created (see the section “Managing Database Roles,” later in this chapter), rather than add a list of users who belong to the role, you specify a password. To obtain the permissions associated with the role, the connection must set the role and supply the password. This is done using the stored procedure sp_setapprole. You set the role to the sales application role (with the password PassW0rd) as follows:

Click here to view code image

EXEC sp_setapprole 'sales', 'PassW0rd'

You can also encrypt the password:

Click here to view code image

EXEC sp_setapprole 'sales', {ENCRYPT N ' PassW0rd'}, 'odbc'

When an application role is set, all permissions ...