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:
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.
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.