SQL Server Application Role vs. regular logins and users

Question

What is the advantage of using a SQL Server application role to manage permissions vs. using standard logins/users and granting the necessary permissions to said users?

We have been using application roles which require the following scenario:

1. Connect to SQL Server using a SQL Server login and password.
2. Activate an application role by passing the role name and another password to sp_setapprole.

I don't see how that's any better or more secure than just granting the application role's permissions to the login/user. Both passwords must be available to the application and anyone who gains access to the login password can probably gain access to the app role password and call sp_setapprole from their own program or in SSMS. Right?

EDIT: As Ed Harper surmised, all instances of the application use the same login in my scenario.

Answer 1

I've never used application roles (CREATE APPLICATION ROLE).
I really can't see the point of them

I have used database roles and added users as members

CREATE ROLE WebUsers AUTHORIZATION dbo;
ALTER ROLE WebUsers ADD MEMBER Tom;
ALTER ROLE WebUsers ADD MEMBER Dick;
ALTER ROLE WebUsers ADD MEMBER Harry;

CREATE ROLE WebAdmins AUTHORIZATION dbo;
ALTER ROLE WebAdmins ADD MEMBER Tom;

Roles have the permissions, not the user

GRANT EXEC TO WebAdmins;

GRANT EXEC On SCHEMA::WebCode TO WebAdmins;

Answer 2

It's not completely clear from your description, but it sounds like you might be using a SQL login assigned at application level - i.e. all instances of the application (assuming there is more than one instance) use the same login/password. In that scenario, using an application role adds very little value.

As I understand it, application roles are intended to be used where each user has their own login in SQL Server (perhaps in a scenario where access to the database is granted by AD authentication), but you don't want to grant users the same rights as applications they use; this assumes the application connects to the database with the AD user's identity, then elevates its permissions by using sp_setapprole. I've never seen this approach used in a production system.